Lesson 03

SAS® Macro Language 1: Essentials
Lesson 03, Section 1 Demo: Using Macro Functions

In this demonstration, we use macro statements and functions to create and populate macro variables. We use the macro variables to create a detail listing of the 2015 storms with winds exceeding 150 miles per hour.

Reminder: If you restarted your SAS session, you must recreate the MC1 library so you can access your practice files. In SAS Studio, open and submit the libname.sas program in the EMC1V2 folder. In Enterprise Guide, run the Autoexec process flow.

%SYSEVALF(argument)
%SYSFUNC(function(argument(s))<, format>)
%STR(argument)

Open m103d02.sas from the demos folder and review the program.
```
%let year=2015;
%let windm=150;

title1 "&year Storms";
title2 "Winds Exceeding &windm M/H";
footnote "Report Created on &sysdate9 at &systime";
proc print data=mc1.storm_final noobs;
   where Season=&year and MaxWindMPH>=&windm;
run;
title;footnote;
```
The %LET statements create two macro variables: Year with a value of 2015 and WindM with a value of 150. These macro variables are referenced in the TITLE statements and in the WHERE statement in the PROC PRINT step. Two automatic macro variables, Sysdate9 and Systime, are used in the FOOTNOTE statement to print the day and time the SAS session began.

Run the program and view the output. Verify that the report includes six storms, and that the macro variable values are displayed in the title and footnote.

Suppose we want to display both miles per hour and kilometers per hour in the TITLE2 statement. We call %SYSEVALF, passing it the value of WindM multiplied by 1.61.
```
title2 "Winds Exceeding &windm M/H or %sysevalf(&windm*1.61) KM/H";
```
Run the program and view the output. Verify that the same six storms are returned, and the title now displays 150 miles per hour and 241.5 kilometers per hour.

The FOOTNOTE statement references the automatic macro variables Sysdate9 and Systime, but these represent when the SAS session started, not when the report was generated. The TODAY and TIME functions return the current date and time, so we use %SYSFUNC in the FOOTNOTE statement to call these non-macro functions.
```
footnote "Report Created on %sysfunc(today(), date9.) at 
%sysfunc(time(), timeampm.)";
```
To include the current date, the first argument to %SYSFUNC is the TODAY function and the second argument is the format we want, DATE9. Then we call %SYSFUNC again, specifying the TIME function and the format TIMEAMPM.

Run the program and view the output. Verify that the date and time in the footnote are current.

We might want to use this FOOTNOTE statement in other places in the program, so we'll store it in a macro variable. After it's created, each time we reference the macro variable, the FOOTNOTE statement will be placed on the input stack, tokenized, compiled, and executed.
```
%let dtfoot=%str(footnote "Report Created on 
%sysfunc(today(),date9.) at %sysfunc(time(),timeampm.)";);
```
We use a %LET statement to assign the FOOTNOTE statement to a macro variable named dtfoot. Because it's a full statement, including the semicolon, we need to use a macro quoting function. But should we use %STR or %NRSTR? The text of the footnote includes the call to %SYSFUNC, and we want this to resolve and execute, so we use %STR. This masks special characters like the semicolon, but still resolves macro triggers. Notice that we pass the entire FOOTNOTE statement to the %STR function.

We replace the previous FOOTNOTE statement with the &dtfoot macro variable reference. Notice there's no semicolon after &dtfoot because the macro variable's value is a complete FOOTNOTE statement, including the semicolon.

%let year=2015;
%let windM=150;
%let dtfoot=%str(footnote "Report Created on %sysfunc(today(), 
date9.) at %sysfunc(time(), timeampm.)";);

title1 "&year Storms";
title2 "Winds Exceeding &windm M/H or %sysevalf(&windm*1.61) KM/H";
&dtfoot
proc print data=mc1.storm_final noobs;
   where MaxWindMPH>=&windm and Season=&year;
run;
title;footnote;

Run the program one last time and view the output. Verify that the date and time are accurate in the footnote.

SAS® Macro Language 1: Essentials
Lesson 03, Section 2 Demo: Creating Macro Variables with a PROC SQL Query

In this demonstration, we use the INTO clause in PROC SQL to write SQL query results into macro variables.

SELECT column INTO :macvar <TRIMMED>
SELECT column INTO :macvar SEPARATED BY "characters"

Open m103d04.sas from the demos folder and review the program.
```
proc sql;
select mean(cost) 
    into :avgcost
    from mc1.storm_damage;
quit;

%put &=avgcost;
%put &=sqlobs;
```
The query in the PROC SQL step calculates the mean of the Cost column from the mc1.storm_damage table and uses the INTO clause to write the result to the macro variable named Avgcost. The %PUT statements write the values of the Avgcost and Sqlobs macro variables to the log.

Run the code. View the results and the log.

The query creates a report with a single value, 2.238E10. This is the mean Cost, and it's the value returned from the query. In the log, we see the same value stored in the macro variable Avgcost, and Sqlobs is 1 because one row was returned by the query.

Suppress the report and format the mean.
```
proc sql noprint;
select mean(cost) format=dollar20. 
    into :avgcost
    from mc1.storm_damage;
quit;

%put &=avgcost;
%put &=sqlobs;
```
We use the NOPRINT option on the PROC SQL statement to suppress the report. We use the FORMAT=DOLLAR20. column modifier to format the mean of Cost.

Run the modified code and verify that there's no report. View the log.

The value stored in the macro variable Avgcost is easier to read. It's formatted with a dollar sign and commas, but now includes leading spaces.

Remove the leading spaces before assigning the calculated value into avgcost.
```
proc sql noprint;
select mean(cost) format=dollar20.
    into :avgcost trimmed
    from mc1.storm_damage;
quit;

%put &=avgcost;
%put &=sqlobs;
```
We added the TRIMMED modifier after Avgcost in the INTO clause to eliminate the leading spaces.

Run the modified code and view the log.

In the log, the value of Avgcost no longer contains leading spaces.

The next PROC SQL step creates multiple macro variables.
```
proc sql noprint;
select mean(cost) format=dollar20.,
       median(cost) format=dollar20. 
    into :avgcost trimmed,
         :medcost trimmed
    from mc1.storm_damage;
quit;

%put &=avgcost;
%put &=medcost;
%put &=sqlobs;
```
The SELECT list includes another column definition to calculate the median of Cost, formatted with DOLLAR20. The INTO clause defines a second macro variable named Medcost and also includes the TRIMMED modifier. The two values returned from the SELECT statement will be loaded sequentially into the two macro variables listed on the INTO clause. The new %PUT statement writes the value of Medcost in the log.

Run the PROC SQL step and the %PUT statements. View the log.

In the log, we see that Medcost is 8.6 billion dollars, and that the macro variable values are formatted with leading spaces trimmed.

Now let's use PROC SQL to create a series of macro variables.
```
proc sql;
select *
	from mc1.storm_type_codes;
quit;
```
This SQL query uses SELECT * to select all columns from the mc1.storm_type_codes table.

Run this step and view the results.

The report contains five rows. Each row displays a value for Type and StormType.

Next, suppress the printed report and load each value of StormType into a numbered series of macro variables starting with Type1.
```
proc sql noprint;
select StormType
	into :Type1-
	from mc1.storm_type_codes;
quit;

%put &=type1 &=type2 &=type3 &=type4 &=type5; 
%put &=sqlobs;
```
In the INTO clause, we specify :TYPE1-. Remember it isn't necessary to specify an upper bound for the range. SAS will create as many macro variables as necessary to store each value returned from the query. The %PUT statements write the values of TYPE1 through TYPE5 and SQLOBS to the log.

Run the PROC SQL step and the %PUT statements. View the log.

The values of StormType are stored in the Type1 through Type5 macro variables, and the value of SQLOBS is 5.

Finally, we load all values of StormType into a single macro variable named TypeList.
```
proc sql noprint;
select StormType
	into :typelist separated by ", "
	from mc1.storm_type_codes;
quit;

%put &=typelist; 
%put &=sqlobs;
```
The SEPARATED BY modifier specifies that the delimiter will be a comma and a space, and the %PUT statement writes the value of TypeList to the log.

Run this step and the %PUT statements. View the log.

The log shows that all five values of StormType are stored in the TypeList macro variable as a comma-delimited list.

SAS® Macro Language 1: Essentials
Lesson 03, Section 3 Demo: Creating Macro Variables with the DATA Step

In this demonstration, we use the SYMPUTX CALL routine in a DATA step to create and populate macro variables.

CALL SYMPUTX(macro-variable-name, value <,scope>)

Open m103d06.sas from the demos folder and review the program.
```
/* Section 1 */
proc means data=mc1.storm_damage noprint;
    var Cost;
    output out=work.sumdata mean= median= /autoname;
run;

data _null_;
    set sumdata;
    /*insert CALL SYMPUTX statements */
run;

%put &=avgcost &=medcost;

/* Section 2 */
proc print data=mc1.storm_type_codes;
run;

data _null_;
	 set mc1.storm_type_codes;
	 call symputx("type", StormType);
run;

%put &=type;
```
The PROC MEANS step calculates the mean and median storm cost from mc1.storm_damage. The analysis variable, Cost, is specified on the VAR statement. We use an OUTPUT statement with the OUT= option to write the calculated mean and median to an output table, work.sumdata. The AUTONAME option tells PROC MEANS to name the output variables automatically, using the name of the analysis variable as a prefix, followed by an underscore and the name of the statistic. So Cost_Mean and Cost_Median will be written to work.sumdata.

Submit the PROC MEANS step and review the output table, work.sumdata.

Cost_Mean is the average cost of damage per storm for all storms, and Cost_Median is the median storm damage. Both have very large numeric values.

We'll store these values in macro variables so that we can access them in subsequent steps.
```
data _null_;
    set sumdata;
    call symputx("avgcost", cost_mean);
    call symputx("medcost", cost_median);
run;

%put &=avgcost &=medcost;
```
The DATA _NULL_ step reads the work.sumdata table created by the PROC MEANS step. We call the SYMPUTX routine to create two macro variables. The first CALL SYMPUTX statement uses a fixed value, avgcost, enclosed in double quotation marks as the macro variable name. It uses Cost_Mean, the variable from the work.sumdata table as its value. In the second CALL SYMPUTX statement, the macro variable name is "medcost" and the value of Cost_Median is assigned.

Run the DATA _NULL_ step along with the %PUT statement. View the log.

In the log, we see the value of avgcost is around 2 billion, and medcost has so many zeros it's hard to read without commas!

Next we'll modify the CALL SYMPUTX statements to format the return value. But how do we supply a format?
```
data _null_;
    set sumdata;
    call symputx("avgcost", put(cost_mean, dollar20.));
	 call symputx("medcost", put(cost_median, dollar20.));
run;
%put &=avgcost &=medcost;
```
We can't use a FORMAT statement, so we use the PUT function as the second argument to CALL SYMPUTX. The PUT function enables us to convert the value from numeric to character and apply a format to the character value. The formats are applied before the values are stored in the symbol table. The resulting values are character, but no conversion messages are written to the log.

In the first CALL SYMPUTX statement, we added a call to the PUT function. For avgcost, the first argument to PUT is Cost_Mean, and the second argument is the format, DOLLAR20.

We modified the second CALL SYMPUTX statement the same way. The first argument to PUT is Cost_Median, and the second argument is the DOLLAR20. format.

Submit the revised DATA _NULL_ step and the %PUT statement. View the log.

The average cost of storm damage is about $22.4 billion, and the median cost is approximately $8.6 billion. The values are much easier to read with the dollar signs and commas.

SAS® Macro Language 1: Essentials
Lesson 03, Section 4 Demo: Indirect References to Macro Variables

In this demonstration, we use a series of macro variables as lookup tables with indirect referencing.

&&prefix&varname
&&&varname

Open m103d08.sas from the demos folder and review the program.
```
%let year=2016;
%let cat=2;
%let basin=SI;

proc sql noprint;
select MinWind
    into :wind1-
    from mc1.storm_cat;
quit;

data _null_;
    set mc1.storm_basin_codes;
    call symputx(Basin, BasinName);
run;

title1 "&basin &year Category &cat+ Storms";
proc print data=mc1.storm_final noobs;
	 where Basin="&basin" and
          MaxWindMPH>=&&wind&cat and
          Season=&year;
run;
title;
```
The %LET statements create three macro variables. Year is assigned 2016, Cat is assigned the value 2, and Basin is assigned SI, the code for the South Indian basin.

The PROC SQL step creates a numbered series of macro variables starting with Wind1 and assigns the values of the MinWind column from the mc1.storm_cat table. The values represent the minimum wind speed for each storm category. For example, Wind1 will be assigned the minimum wind speed for Category 1 storms, and Wind2 will be assigned the minimum wind speed for Category 2 storms.

The DATA step creates a series of macro variables with names derived from the Basin column and values assigned from the BasinName column in the mc1.storm_basin_codes table. For example, the macro variable SI has a value of South Indian.

The TITLE statement and PROC PRINT step include direct macro variable references: &basin, &year, and &cat. The WHERE statement also includes an indirect macro variable reference, &&wind&cat, which resolves to Wind1, Wind2, and so on, up to Wind5, depending on the value of cat. So, when Cat=2, &&wind&cat resolves to &wind2, and on the second pass, that resolves to 96, the minimum wind speed for Category 2 storms.

Submit the program and view the results.

The PROC PRINT output lists three storms that occurred in 2016. Notice that the title, SI 2016 Category 2+ Storms, includes the values that we assigned to Basin, Year, and Cat.

We'll go back to the Code tab and modify the TITLE statement.
```
title1 "&&&basin &year Category &cat+ Storms";
```
Instead of the direct reference, &basin, we'll use an indirect macro variable reference with three ampersands. On the first scan, two ampersands resolve to one ampersand and &basin resolves to SI. On the second scan &SI resolves to South Indian.

Submit the revised program and view the results.

Now the title includes South Indian instead of SI.

Test the program with other values to verify that the results change accordingly. Now the %LET statements assign 2014 to Year, 3 to Cat, and NA to Basin.

%let year=2014;
%let cat=3;
%let basin=NA;

proc sql noprint;
select MinWind, Damage
    into :wind1-, :damage1-
    from mc1.storm_cat;
quit;

data _null_;
    set mc1.storm_basin_codes;
    call symputx(Basin, BasinName);
run;

title1 "&&&basin &year Category &cat+ Storms";
proc print data=mc1.storm_final noobs;
	where Basin="&basin" and
		  MaxWindMPH>=&&wind&cat and
		  Season=&year;
run;
title;

Submit the program and view the results.

The PROC PRINT output includes two storms, and the title is North Atlantic 2014 Category 3+ Storms.

Next, we add a footnote to display the storm category and the corresponding damage description.
```
%let year=2014;
%let cat=3;
%let basin=NA;

proc sql noprint;
select MinWind, Damage
    into :wind1-, :damage1-
    from mc1.storm_cat;
quit;

data _null_;
    set mc1.storm_basin_codes;
    call symputx(Basin, BasinName);
run;

title1 "&&&basin &year Category &cat+ Storms";
footnote "Category &cat storms typically cause &&damage&cat";
proc print data=mc1.storm_final noobs;
	where Basin="&basin" and
		  MaxWindMPH>=&&wind&cat and
		  Season=&year;
run;
title;footnote;
```
The descriptions are stored in the mc1.storm_cat table. This table has three columns: Category, MinWind, and Damage with values for storm categories 1 through 5. The Damage column contains a description of the type of damage that the category causes.

The modified SQL query selects an additional column, Damage, and load its values into a range of macro variables beginning with Damage1. The FOOTNOTE statement includes a direct reference to the selected storm category and an indirect reference to the corresponding damage description.

Submit the revised program and view the results.

The footnote is displayed, but the first letter of the damage description is capitalized.

Modify the FOOTNOTE statement.

%let year=2014;
%let cat=3;
%let basin=NA;

proc sql noprint;
select MinWind, Damage
    into :wind1-, :damage1-
    from mc1.storm_cat;
quit;

data _null_;
    set mc1.storm_basin_codes;
    call symputx(Basin, BasinName);
run;

title1 "&&&basin &year Category &cat+ Storms";
footnote "Category &cat storms typically cause %lowcase(&&damage&cat)";
proc print data=mc1.storm_final noobs;
	where Basin="&basin" and
		  MaxWindMPH>=&&wind&cat and
		  Season=&year;
run;
title;footnote;

In the FOOTNOTE statement we pass the resolved value of the indirect reference to the %LOWCASE function.

Submit the program, and view the results.

Now the footnote is correct.

It's always a good practice to test the modified program with other values to make sure that it produces the correct results. So we modified the %LET statements, changing the value of Year to 2015, Cat to 2, and Basin to WP.

%let year=2015;
%let cat=2;
%let basin=WP;

proc sql noprint;
select MinWind, Damage
    into :wind1-, :damage1-
    from mc1.storm_cat;
quit;

data _null_;
    set mc1.storm_basin_codes;
    call symputx(Basin, BasinName);
run;

title1 "&&&basin &year Category &cat+ Storms";
footnote "Category &cat storms typically cause %lowcase(&&damage&cat)";
proc print data=mc1.storm_final noobs;
	where Basin="&basin" and
		  MaxWindMPH>=&&wind&cat and
		  Season=&year;
run;
title;footnote;

Submit the program one more time and view the results.

The title is West Pacific 2015 Category 2+ Storms, and the report contains 16 storms. The footnote, Category 2 storms typically cause extensive damage, is correct.

Lesson 04

SAS® Macro Language 1: Essentials
Lesson 04, Section 1 Demo: Defining a Macro with Parameters

In this demonstration, we produce a report about North Atlantic storms in the 2016 season with maximum winds greater than 80 miles per hour. We want to generalize the program so that we can produce the report for other seasons, basins, and wind speeds.

&&prefix&varname
&&&varname

Open m104d02.sas from the demos folder and review the program.

title1 "Storm Frequency by Type";
title2 "NA Basin, 2016 Season, Max Wind > 80 MPH"; 
proc sgplot data=mc1.storm_final;
    vbar StormType / dataskin=pressed;
    where Basin="NA" and Season=2016 and MaxWindMPH>80; 
run;
title;

The program calls PROC SGPLOT to produce the bar chart showing the frequency of StormType by Basin.

Run the program and view the results.

The plot includes three bars, there are seven storms, and the title includes the requested values: the NA basin, the 2016 season, and winds exceeding 80 MPH.

Now we want to generalize this program.

%let Basin=NA;
%let Season=2016;
%let MaxWind=80;

title1 "Storm Frequency by Type";
title2 "&Basin Basin, &Season Season, Max Wind > &MaxWind MPH";
proc sgplot data=mc1.storm_final;
    vbar StormType / dataskin=pressed;
    where Basin="&Basin" and Season=&Season and MaxWindMPH>&MaxWind; 
run;
title;

We use %LET statements to create macro variables named Basin, Season, and MaxWind, and assign values of NA, 2016, and 80 respectively. Then we replace the hardcoded values in the program with the appropriate macro variable references.

Run the program and view the results and the log.

Verify that it reproduces the original report with no errors or warnings in the log.

Before we create a macro definition, let's test this generalized program with other values to make sure it generates a report based on the revised values.

%let Basin=EP;
%let Season=2015;
%let MaxWind=125;

title1 "Storm Frequency by Type";
title2 "&Basin Basin, &Season Season, Max Wind > &MaxWind MPH"; 
proc sgplot data=mc1.storm_final;
    vbar StormType / dataskin=pressed;
    where Basin="&Basin" and Season=&Season and MaxWindMPH>&MaxWind; 
run;
title;

The modified %LET statements change the value of Basin to EP, Season to 2015, and MaxWind to 125.

Submit the program and view the results and the log.

It runs without errors or warnings, and the report and title reflect the new values of EP basin in the 2015 season with maximum wind speeds greater than 125 miles per hour. The chart shows nine storms and two bars or storm types.

Now that the program is validated, let's convert it to a macro.

options mcompilenote=all;
%macro StormChart(Basin, Season, MaxWind);
title1 "Storm Frequency by Type";
title2 "&Basin Basin, &Season Season, Max Wind > &MaxWind MPH"; 
proc sgplot data=mc1.storm_final;
    vbar StormType / dataskin=pressed;
    where Basin="&Basin" and Season=&Season and MaxWindMPH>&MaxWind; 
run;
title;
%mend StormChart;

We add the %MACRO and the %MEND statements to define the StormChart macro. It accepts three positional parameters, Basin, Season, and MaxWind, so we've removed the three %LET statements.

Submit the MCOMPILENOTE=ALL options statement and the macro definition and view the log.

A note in the log shows that the macro completed compilation without errors.

Call the macro with the MPRINT option enabled.
```
options mprint;
%StormChart(EP,2015,125)
options nomprint;
```
Set the MPRINT option so that the generated code is written to the log and call the StormChart macro with parameter values EP for Basin, 2015 for Season, and 125 for MaxWind. (The same values we used when we generated the last report). Then disable the MPRINT option. MPRINT can produce a lot of messages, so it's best to use it only when you're developing a macro.

Notice that the keywords in the first OPTIONS statement are displayed in blue, but they're not color-coded in the second OPTIONS statement. This is because the macro call doesn't end with a semicolon. The OPTIONS statement is correct, even though the coloring is different than you might expect. If the syntax wasn't correct, an error message would be written to the log.

Highlight and submit the three statements and view the results and the log.

The bar chart matches the chart produced in the previous report, and the log includes MPRINT messages that show the generated code with the macro variables resolved.

Demo Steps for SAS® Macro Language 1: Essentials

Lesson 03

SAS® Macro Language 1: Essentials
Lesson 03, Section 1 Demo: Using Macro Functions

SAS® Macro Language 1: Essentials
Lesson 03, Section 2 Demo: Creating Macro Variables with a PROC SQL Query

SAS® Macro Language 1: Essentials
Lesson 03, Section 3 Demo: Creating Macro Variables with the DATA Step

SAS® Macro Language 1: Essentials
Lesson 03, Section 4 Demo: Indirect References to Macro Variables

Lesson 04

SAS® Macro Language 1: Essentials
Lesson 04, Section 1 Demo: Defining a Macro with Parameters

Lesson 05

Lesson 03

SAS® Macro Language 1: Essentials Lesson 03, Section 1 Demo: Using Macro Functions

SAS® Macro Language 1: Essentials Lesson 03, Section 2 Demo: Creating Macro Variables with a PROC SQL Query

SAS® Macro Language 1: Essentials Lesson 03, Section 3 Demo: Creating Macro Variables with the DATA Step

SAS® Macro Language 1: Essentials Lesson 03, Section 4 Demo: Indirect References to Macro Variables

Lesson 04

SAS® Macro Language 1: Essentials Lesson 04, Section 1 Demo: Defining a Macro with Parameters

Lesson 05

SAS® Macro Language 1: Essentials
Lesson 03, Section 1 Demo: Using Macro Functions

SAS® Macro Language 1: Essentials
Lesson 03, Section 2 Demo: Creating Macro Variables with a PROC SQL Query

SAS® Macro Language 1: Essentials
Lesson 03, Section 3 Demo: Creating Macro Variables with the DATA Step

SAS® Macro Language 1: Essentials
Lesson 03, Section 4 Demo: Indirect References to Macro Variables

SAS® Macro Language 1: Essentials
Lesson 04, Section 1 Demo: Defining a Macro with Parameters