How to automate your code in SAS - Three ways to write Macros

I have given three types - “How to write Macros in SAS”. This is for beginners, so I hope I have made it easy without compromising on technical details.

Before knowing How, it is important to know Why. So I will start with a real scenario and explain Why Macros are important in everyday programming.

Macros are used to automate your program so that you don’t have to write the same piece of code again and again. This automation can be as simple as asking SAS to remember a single value to remember complex joins or even remembering the pages of code which you want to re-use.   

Scenario:

You have been requested to write a report which looks at all customers who bought product “Shoes” launched after 01-Jan-2014. This report does several things like number of pairs sold, region of sales, weekly calls in the call centre, summarizes data, exports to excel etc. Let’s say your code is around 1000 lines. What if you need to look at different products with different launch date every week? One way is to look through 1000 lines of code every week and change Product and launch Date wherever it appears in the code OR something like “Find and Replace” would be a life saver here?

Suppose this was your original code (1000 + lines)...

Data New_Table;

Set Old_Table;

where Product_Name = &Product_N. and Launch_Date = &Launch_D.

run;

----

-----

Type 1 -%Let (Easiest of all Macros - 'Find and Replace'). This works as Find and Replace in MS Word or Excel

This is how you will create a Macro before you start writing your code:

%let Product_N = “Shoes”; 

%let Launch_D = “01JAN2014”;

Followed by your original code……

How this works:

a) Create a Macros: %let Product_N = “Shoes”; 

• %Let keyword tells SAS that you have created a Macro.
• Then you give a name to your macro - here you have created two macros Product_N and Launch_D. This can be anything but give it a name which is easy to remember when you read the code next time.
• Then you give that macro a value with the help of '=' sign. This is the value that can change. For example Product and Launch Date can change next time. 

b) Call the Macro that you have created above in your code: 

•  Instead of using values of the variables in your code, now you use the Macro names (&Product_N.) with & sign. For example: where Product_Name = &Product_N.  

So basically it works like this - Where ever SAS finds &Product_N in your code, it will replace it with the value you have given to the macro when you declared it. In this case its "Shoes".

 Next time, all you need to do is change the value of the Macro (%let = ) and SAS will replace the Macro (&Product_N) with this value. 

Type 2 – Call Symput

In %let example you saw above, you can easily assign values to the Macro variables after = sign. Another common scenario is that when you want SAS to calculate a value and then assign it to a Macro variable automatically. 

Suppose you want to compare sales at two different dates - a day before product was launched and sales a day after Launch_date. So basically you want SAS to look into products table and calculate two new dates a) Date_before_launch and b) Date_after_launch from Launch_Date. 

You can achieve this easily by call symput routine. Your code will look something like this:

Data _null_;

Set Products;

Call Symput (‘Date_before_launch’, Launch_Date – 1);

Call Symput (‘Date_after_launch’, Launch_Date + 1);

Run;

How this works: 

Data _Null_: You don’t need to create a new table so call it _null_.

Call Symput: Call Symput is a call routine in SAS. It has two parameters separated by a comma (,). First parameter is the Macro_Name (name you want to give to your macro) and second parameter is the value calculated from some existing variable - in this case Launch_Date - 1. 

You use this macro in the same way as you did in example 1:

where sales_date = &date_before_launch.;

Few things to remember while using Call Symput:

• Macro variables created with Call_Symput call routine can only be used in the next SAS program. It cannot be used in the same program.
• It can be used both for Character and numeric variables.
• You can also mention pre-defined macro in SAS as the second parameter. For example, to calculate yesterday’s date, you can do: 
• Call Symput(“Yest_Date”,today()-1); /*Today() is the pre-defined variable in sas which stores today's date*/

  

Type 3–Proc SQL Into (This is easy and my favorite)

What you did earlier with Call Symput, you can do the same with Proc SQL’s Into statement. Same results can be achieved with this code:

Proc sql noprint;

Select (Launch_Date – 1) into :date_before_launch                  /*This will be the macro*/

From Products;

Quit; 

Another Example: Suppose you want to create a macro “Rows” which stores number of rows in Products table and Maximum sales into macro “Max_Sales”.

Proc SQL noprint;

Select Count(*) into :Rows

Max(Sales) into :Max_Sales

From Products;

Quit; 

Any comments / suggestions, please post below.