As a data crunching and manipulation language, SAS offers you a variety of data-centric, efficient processes to deal with large sums of data.
SAS programs with repetitive code blocks tend to become lengthy and code-heavy; plus, manual scripting can become laborious and monotonous, increasing your margin for errors.
So, how can you automate code sections to perform repetitive tasks?
This is where SAS macros come into play. Read on to find out what SAS macros are and how they improve SAS efficiency.
What Are SAS Macros?
Macros are variables that you can employ in SAS codes to automate repetitive tasks. When using macros as coding elements, macro variables store dynamic character and numerical values, including names, letters, and numbers.
A macro code comprises two building blocks, the macro itself and the macro variable. SAS programs require you to differentiate between them using & and % signs.
For example, if you write &Employee, you’re calling the macro variable. But, if you write %Employee, you’re defining the macro variable.
Why Use SAS Macros?
There are several benefits to using macros in your SAS code. They can help you:
- Substitute repetitive lines of code with one single variable
- Commit changes to one variable and reflect it in multiple sections across the program
- Input real-time data values to make your programs data-driven and effective
Types of SAS Macros
There are some built-in macros that you’ll commonly use in SAS. They’ll prove pretty valuable for real-time applications on a use-case basis.
1. %Let Statement
You can use the %let macro for any string or numerical values. It has the following characteristics:
● The scope of the variable is between 0 – 65,534
● Mathematical expressions within a macro variable aren’t evaluated
● Freely store numerical values within a string
● You can keep quotation marks as a part of a character value
● Any blanks with leading or trailing are removed before assignment
To declare a %let macro, use the macro variable name preceded by an ampersand (&).
Here’s the syntax of a common %let macro:
%let macro variable = valueAs a pre-requisite, the macro variable name should be devoid of any spaces, limited to 32 characters in length, and shouldn’t start with a numeric value.
Here’s an example of how you can use the %let statement in its simplest form:
%let age = 10;data sample;
input name $ age sex $;
cards
;
Rob 30 M
Alan 10 M
Jerry 20 M
Monica 35 F
;
run;
proc sort data = sample (where = (age NE &age.));
by age;
run;
This is how the code looks in a SAS environment:
In the output, the values in the age column that aren’t equal to 10 are filtered as soon as the condition is met. The macro variable is defined next to the %let statement, which is used to equate the values in the condition.
If you want to execute the values after changing the filter condition, all you have to do is change the value from 10 to the desired number in the %let statement. It’s that simple; run the code again to see the new results.
Here’s what the output looks like when the code is executed:
2. Macro Parameters
Macro parameters are rather helpful when you want to define the variables and steps within a set of macro statements directly. The whole block of statements can be reused to perform various functions, as and when needed.
For example, suppose you want to sort an existing dataset based on certain criteria.
data test_example;
input ID $ name $ age;
cards;
001 John 20
002 Jack 30
003 Ron 15
001 Jack 30
005 Aon 18
;
run;%macro sorting (in_ds= , sort_var =, out_ds=);
proc sort data = &in_ds nodupkey out = &out_ds;
by &sort_var;
run;
%mend;
In the first block of code, create a dataset with some dummy values. Once the data is available, you can create a macro statement within the %macro and %mend statements.
In the %macro statement, you can define the name of the macro, along with the local variables, which would hold certain values when the macro is called.
Finally, end the macro code block with the %mend statement.
To call the macro, pass the macro statement in the following manner:
%sorting (in_ds= test_example, sort_var = name , out_ds=test_example_new);Where:
- %sorting: macro variable name created in the macro statement
- in_ds: the dataset name to be sorted
- sort_var: variable to use while sorting the dataset
- out_ds: name of the resulting dataset, which will be created after the dataset is sorted
3. Using proc sql as a Macro Variable
SQL is one of the most commonly used languages within SAS for data manipulation and data processing. Having said this, it’s imperative to be able to store values in an SQL variable for further use. Macros and SQL are a strong combination, and using them together can go a long way.
For example, if you have a list of values you’d like to store in a macro variable, you can use the Proc SQL statement to create a macro variable.
To do so, follow this example:
proc sql;
select variable_name into: macro_variable_name separated by ','
from table_name;You can use conditions to filter the variable values using a where statement if you’d like to do so. Otherwise, with the normal conditions, you can still save values in a single variable. This includes character and numeric values.
Here’s an example to demonstrate the creation of a macro variable using proc sql:
proc sql;
select age into: age separated by ','
from test_example;
The output contains a list of all age values from the table test_example, each of which is separated by a comma. This can be used to filter information in another Proc SQL statement when you’re pulling information from the server tables.
Output:
Using SAS Macros to Automate Repetitive Tasks
This list of macros is not exhaustive. There are a few other variations that can make your life easier, especially if you code every day.
However, macros aren’t restricted to just SAS. There are a few macro variations available in VBA that can be used to automate daily repetitive tasks in Excel, Word, and other MS Office applications.
If you’re an avid fan of automation, give VBA macros a try.
Read Next
About The Author
