Activities and Practices (with Solutions) for SAS® Macro Language 1: Essentials

Level 1 Practice: Defining and Using Macro Variables for Substitution

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.

1. Open the m102p01.sas program from the practices folder. Submit the program and review the log and output. Verify that the title is US Customers Ages 18 to 24 and that 127 rows were read.
   
   
   
2. Create a macro variable named Country and assign the value US. Replace all occurrences of US with references to Country. Submit the program and verify that the results are the same as step 1.
   
   

   Solution:

   %let Country=US;
   title "&Country Customers Ages 18 to 24";
   proc print data=mc1.customers;
       var Name Age Type;
       where Country = "&Country" 
             and Age between 18 and 24;
   run;
   title;

   
   
3. Modify the value of Country to FR. Resubmit the program and verify that the title is FR Customers ages 18 to 24 and that 46 rows were read.
   
   

   Solution:

   %let Country=FR;
   title "&Country Customers Ages 18 to 24";
   proc print data=mc1.customers;
       var Name Age Type;
       where Country = "&Country" 
             and Age between 18 and 24;
   run;
   title;

   
   
4. Modify the program.
   • Create additional macro variables, Age1 and Age2.
   • Set Age1 to 25, Age2 to 34, and Country to AU.
   • Replace all occurrences of 18 and 24 with references to Age1 and Age2.
   Submit the program.
   
   

   Solution:

   %let Country=AU;
   %let age1=25;
   %let age2=34;
   title "&Country Customers Ages &age1 to &age2";
   proc print data=mc1.customers;
       var Name Age Type;
       where Country = "&Country" 
             and Age between &age1 and &age2;
   run;
   title;

   
   
5. What is the report title?
   
   

   Solution:

   The title is AU Customers Ages 25 to 34.
   
6. How many rows were read?
   
   

   Solution:

   There were ten rows read.
   

Level 2 Practice: Using Macro Variable References with Delimiters

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.

1. Open the m102p02.sas program from the practices folder. Submit the program and review the results. Verify that the report contains 17 rows.
   
   
   
2. Modify the program.
   • Create macro variables Lib, Dsn, and Var and assign the values mc1, newhires, and Employee respectively.
   • Modify every occurrence of mc1, newhires, and Employee so that they are replaced by references to the corresponding macro variable.
   Submit the program.
   
   Note: Be sure to include the 's' in Employees as part of the title text.
   
   

   Solution:

   %let lib=mc1;
   %let dsn=newhires;
   %let var=Employee;
   title "Listing of All &var.s From &lib..&dsn";
   proc print data=&lib..&dsn;
       var &var._Name &var._ID;
   run;
   title;

   
   
3. What is the report title?
   
   

   Solution:

   Listing of All Employees from mc1.newhires
   
4. How many rows are in the report?
   
   

   Solution:

   There are seventeen rows in the report.
   

Level 1 Practice: Using the %UPCASE and %SCAN Functions

1. Open m103p01.sas from the practices folder. Add a %LET statement to convert the value of FullName to uppercase and assign the result to FullName. Write a single %PUT statement to display FullName in a sentence using both its current form and proper case, as shown below. Submit the program and view the log to verify the results.
   
   Note: Use %SYSFUNC to execute the PROPCASE function.
   
   

   ANTHONY MILLER in proper case is Anthony Miller. 

   
   

   Solution:

   %let fullname=AnTHoNY MilLeR;
   %put &fullname;
   
   %let fullname=%upcase(&fullname);
   %put &fullname in proper case is %sysfunc(propcase(&fullname)).;
   

   
   
2. Modify and submit the program.
   • Add a %LET statement to extract the first name from FullName, convert it to proper case, and assign the result to a macro variable named First.
   • Add another %LET statement to extract the last name from FullName, convert it to proper case, and assign the result to a macro variable named Last.
   • Display the values of FullName, First, and Last in the log as shown below.
     
     

     FULLNAME=ANTHONY MILLER FIRST=Anthony LAST=Miller

   
   

   Solution:

   %let first=%sysfunc(propcase(%scan(&fullname,1)));
   %let last=%sysfunc(propcase(%scan(&fullname,-1)));
   %put &=fullname &=first &=last;
   

   
   
3. Add a %SYMDEL statement to delete FullName, First, and Last from the global symbol table. Use a %PUT statement to write the values of all user-defined macro variables to the log. Submit the two statements and verify that that FullName, First, and Last are no longer listed.
   
   

   Solution:

   %symdel fullname first last;
   %put _user_;
   

   
   

Level 2 Practice: Using Macro Quoting Functions

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.

1. Open m103p02.sas from the practices folder and review the code. Submit the program and review the results. Note that the footnote includes the date and time that the SAS session started.
   
   
   
2. Modify the program.
   • Create a macro variable named Product and assign the value R&D.
   • Reference Product in the TITLE and WHERE statements, replacing Jacket.
   • Modify the FOOTNOTE statement to display the current date and time, using the DATE9 and TIMEAMPM9 formats respectively.
   
   

   Solution:

   %let product=%nrstr(R&D);
   title "Product Names Containing '&product'";
   footnote "Report Produced %sysfunc(date(), date9.) %sysfunc(time(),timeampm9.)";
   proc print data=mc1.products;
       where Product_Name contains "&product";
       var Product_Name Product_ID Supplier_Name;
   run;
   title;
   footnote;
   

   
   
3. Submit the program and verify that the title is Product Names Containing 'R&D' and that the current date and time are displayed in the footnote.
   • How many rows are in the report?
   
   
   

   Solution:

   
   How many rows are in the report? There are eight rows in the report.
   

Challenge Practice: Using Macro Functions to Perform Calculations

1. Assign your birthdate to a macro variable named Birthdate in the form DDMONYYYY (for example, 01Jan1990). Use %SYSEVALF to calculate your age by subtracting your birthdate from today’s date and dividing the difference by 365.25. Write the result to the log.
   
   For example, if your birthday is January 1, 1990, and today is April 3, 2019, the results would appear as shown below. Hint: Reference Birthdate as a SAS date constant ("&birthdate"d).
   
   Note: The value of age depends on the current date.
   
   

   My age is 29.2183436002737

   
   

   Solution:

   %let birthdate=01Jan1990;
   %put My age is %sysevalf((%sysfunc(today())- 
       "&birthdate"d)/365.25);
   

   
   
2. Use the second argument to %SYSEVALF to return just the integer portion of your age and write the results to the log.
   
   For example, if your birthday is January 1, 1990, and today is April 3, 2019, the results would appear as shown below.
   
   

   My age is 29

   
   

   Solution:

   %put My age is %sysevalf((%sysfunc(today())-
       "&birthdate"d)/365.25, int);
   

   
   

Level 1 Practice: Using PROC SQL to Generate Macro Variables for Use in a Report Title

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.

1. Open m103p04.sas from the practices folder. Review the code and submit the %LET statements and the PROC SQL step. Verify that Qty (the mean for Quantity) is 1.43 and Price (the mean for Total_Retail_Price) is 137.72.
   
   
   
2. In the TITLE2 statement, replace xxx with the mean for Quantity (1.43), and replace yyy with the mean for Total_Retail_Price (137.72). Submit the TITLE statements and the PROC PRINT step, and review the log and results.
   
   • How many rows were read from the input table?
   
   
   

   Solution:

   %let start=01Jan2019;
   %let stop=31Jan2019;
   proc sql noprint;
   select mean(Quantity) format=4.2 as qty, 
          mean(Total_Retail_Price) format=dollar7.2 as price
   	   from mc1.orders
   	   where Order_Date between "&start"d and "&stop"d;
   quit;
   
   title1 " Orders from &start to &stop";
   title2 "Average Quantity: 1.43   Average Price: 137.72";
   proc print data=mc1.orders;
   	where Order_Date between "&start"d and "&stop"d;
   	var Order_ID Order_Date Quantity Total_Retail_Price;
   	sum Quantity Total_Retail_Price;
   	format Total_Retail_Price dollar8.;
   run;
   title;

   
   How many rows were read from the input table? 278 rows
   
3. In the PROC SQL step, add an INTO clause to assign the mean for Quantity to a macro variable named Qty, and the mean for Total_Retail_Price to a macro variable named Price. In the TITLE2 statement, replace the hardcoded mean values with references to Qty and Price.
   
   Submit the entire program and review the log and results. Verify that the title displayed correctly.
   • How many rows were read from the input table?
   • Why is the average price displayed with a leading dollar sign in the title?
   
   
   

   Solution:

   %let start=01Jan2019;
   %let stop=31Jan2019;
   proc sql noprint;
   select mean(Quantity) format=4.2, 
          mean(Total_Retail_Price) format=dollar7.2
       into :qty, :price
       from mc1.orders
       where Order_Date between "&start"d and "&stop"d;
   quit;
   
   title1 " Orders from &start to &stop";
   title2 "Average Quantity: &qty   Average Price: &price";
   proc print data=mc1.orders;
       where Order_Date between "&start"d and "&stop"d;
       var Order_ID Order_Date Quantity Total_Retail_Price;
       sum Quantity Total_Retail_Price;
       format Total_Retail_Price dollar8.;
   run;
   title;

   
   How many rows were read from the input table? 278 rows
   
   Why is the average price displayed with a leading dollar sign in the title? The DOLLAR7.2 format is applied to the mean of Total_Retail_Price before it is assigned to the macro variable, price.
   
4. Modify the %LET statements to assign 01Feb2019 to start and 28Feb2019 to stop. Submit the program again.
   • How many rows were read from the input table?
   • What are the resolved values of qty and price in TITLE2?
   
   
   

   Solution:

   %let start=01Feb2019;
   %let stop=28Feb2019;
   proc sql noprint;
   select mean(Quantity) format=4.2, 
          mean(Total_Retail_Price) format=dollar7.2
   	 into :qty, :price 
   	 from mc1.orders
   	 where Order_Date between "&start"d and "&stop"d;
   quit;
   
   title1 " Orders from &start to &stop";
   title2 "Average Quantity: &qty   Average Price: &price";
   proc print data=mc1.orders;
   	 where Order_Date between "&start"d and "&stop"d;
   	 var Order_ID Order_Date Quantity Total_Retail_Price;
   	 sum Quantity Total_Retail_Price;
   	 format Total_Retail_Price dollar8.;
   run;
   title;

   
   How many rows were read from the input table? 219 rows
   
   What are the resolved values of qty and price in TITLE2? Average Quantity: 1.30 Average Price: $104.43
   

Level 2 Practice: Using PROC SQL to Generate Macro Variables for Use in Subsequent Steps

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.

1. Open m103p05.sas from the practices folder. Review and submit the code in part a. Verify that the reported average wind speed is 79.
   
   
   
2. Review the code in part b. Replace every occurrence of XX with the average wind speed from step a. Submit the code in part b and review the results.
   • How many rows are in the table?
   • Which bar has the highest frequency?
   
   
   

   Solution:

   %let year=2016;
   %let basincode=NA;
   
   title1 "North Atlantic Basin Storms in &year Season";
   title2 "Max Wind > Season Average of 79 MPH"; 
   proc print data=mc1.storm_final noobs;
   	var Name StartDate EndDate MaxWindMPH MinPressure;
   	where MaxWindMPH>79 and Season=&Year and Basin="&basinCode"; 
   run;
   title;
   proc sgplot data=mc1.storm_final;
       where MaxWindMPH>79 and Season=&year and Basin="&basincode"; 
   	vbar StormType;
   	yaxis display=(noline) grid;
   run;

   
   How many rows are in the table? 7 rows
   
   Which bar has the highest frequency? Extratropical
   
3. Modify the PROC SQL step.
   • Suppress the PROC SQL output.
   • Store the calculated value in a macro variable named AvgWind with no leading spaces.
   • Add another SELECT statement to select the BasinName value from mc1.storm_basin_codes where Basin is equal to the basincode macro variable. Write the value to a macro variable named BasinName.
   
   
   

   Solution:

   proc sql noprint;
   select round(mean(MaxWindMPH)) as AvgWind
       into :avgwind trimmed
       from mc1.storm_final
       where Season=&year and Basin="&basincode";
   select BasinName 
      into :basinname
      from mc1.storm_basin_codes
      where basin="&basinCode";
   quit;
   

   
   
4. Modify the report code.
   • Replace all hardcoded values of 79 with a reference to AvgWind.
   • Replace all hardcoded text values of North Atlantic with a reference to BasinName.
   • Submit the modified code and verify that the report contains the same information as the report generated in step b.
   
   
   

   Solution:

   title1 "&basinName Storms in &year Season";
   title2 "Max Wind > Season Average of &avgwind MPH"; 
   proc print data=mc1.storm_final noobs;
   	 var Name StartDate EndDate MaxWindMPH MinPressure;
   	 where MaxWindMPH>&avgwind and Season=&Year 
             and Basin="&basincode"; 
   run;
   title;
   
   proc sgplot data=mc1.storm_final;
       where MaxWindMPH>&avgwind and Season=&year 
             and Basin="&basincode"; 
   	 vbar StormType;
   	 yaxis display=(noline) grid;
   run;
   

   
   
5. Modify the %LET statements to assign 2015 to Year and EP to BasinCode. Submit the entire program, including the modified %LET statements and the PROC SQL step. Review the log to ensure that there are no errors or warnings. Verify that the report title is East Pacific Storms in 2015 Season Max Wind > Season Average of 92 MPH.
   • How many rows are in the table?
   • Which bar has the highest frequency?
   
   
   

   Solution:

   %let year=2015;
   %let basincode=EP;
   
   proc sql noprint;
   select round(mean(MaxWindMPH)) as AvgWind
       into :avgwind trimmed
       from mc1.storm_final
       where Season=&Year and Basin="&basincode";
   select BasinName 
      into :basinname
      from mc1.storm_basin_codes
      where basin="&basincode";
   quit;
   
   title1 "&basinname Storms in &year Season";
   title2 "Max Wind > Season Average of &avgwind MPH"; 
   proc print data=mc1.storm_final noobs;
   	 var Name StartDate EndDate MaxWindMPH MinPressure;
   	 where MaxWindMPH>&avgwind and Season=&Year 
             and Basin="&basincode" ; 
   run;
   title;
   
   proc sgplot data=mc1.storm_final;
      where MaxWindMPH>&avgwind and Season=&year and 
            Basin="&basincode"; 
   	vbar StormType;
   	yaxis display=(noline) grid;
   run;

   
   How many rows are in the table? 11 rows
   
   Which bar has the highest frequency? Disturbance
   

Level 1 Practice: Creating Macro Variables with the SYMPUTX Routine

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.

1. Open m103p06.sas from the practices folder. Review and submit the program. Verify that the report title is New Staff: Administration Department and that the sum of Salary is $221,618.
   
   
   
2. Add a %LET statement to assign the value Administration to a new macro variable named dept, and replace every occurrence of Administration with a reference to dept. Submit the modified program and verify that the title and report are the same as in part a.
   
   

   Solution:

   %let dept=Administration;
   data staff;
       keep Employee_ID Department Job_Title Salary;
       set mc1.newhires;
       where Department="&dept";
   run;
   
   title "New Staff: &dept Department";
   proc print data=staff;
       sum salary;
   run;
   title;

   
   Verify that the report title is New Staff: Administration Department and that the sum of Salary is $221,618.
   
3. Change the value of dept to Sales and submit the program. Verify that the report title is New Staff: Sales Department.
   • What is the sum of Salary?
   
   

   Solution:

   %let dept=Sales;
   data staff;
       keep Employee_ID Department Job_Title Salary;
       set mc1.newhires;
       where Department="&dept";
   run;
   
   title "New Staff: &dept Department";
   proc print data=staff;
       sum salary;
   run;
   title;

   
   What is the sum of Salary? $257,245
   
4. Modify the DATA step to create a macro variable named avg to store the average salary on the last iteration. Hint: Use the PUT function and the DOLLAR9. format when assigning the value to avg.
   
   Add a FOOTNOTE statement before the PROC PRINT step to display the value of avg as shown below. Submit the program and review the results.
   
   

   Average Salary: $xx,xxx

   
   
   • What text is displayed in the footnote?
   
   
   

   Solution:

   %let dept=Sales;
   data staff;
       keep Employee_ID Department Job_Title Salary;
       set mc1.newhires end=last;
       where Department="&dept";
       total+salary;
       if last=1 then 
           call symputx("avg",put(total/_n_,dollar9.));
   run;
   
   footnote "Average Salary: &avg";
   title "New Staff: &dept Department";
   proc print data=staff;
       sum salary;
   run;
   title;footnote;

   
   What text is displayed in the footnote? Average Salary: $36,749
   

Level 2 Practice: Using a DATA _NULL_ Step to Create a Series of Macro Variables

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.

1. Open m103p07.sas from the practices folder and review the program. The sashelp.vmacro data source is a dynamic view that contains the name and value of all macro variables in the current SAS session. Run the program and view the report.
   
   
   
2. Insert a DATA _NULL_ step before the PROC PRINT step to create a series of macro variables using the values stored in the mc1.Storm_Ocean_Codes table. Use the values in Ocean to name the macro variables. Use the values in OceanName as the macro variable values.
   
   
   
3. Modify the PROC PRINT step to display only macro variables with one-character names. Submit the entire program and verify that five macro variables were created.
   • What is the value of the macro variable, S?
   
   
   

   Solution:

   data _null_;
       set mc1.Storm_Ocean_Codes;
       call symputx(Ocean,OceanName);
   run; 
   proc print data=sashelp.vmacro;
       var name value;
       where name like "_";
   run;

   
   What is the value of the macro variable, S? Southern (Antarctic)
   

Level 1 Practice: Using Indirect References to Macro Variables

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.

1. Open m103p08.sas from the practices folder. Submit the code and review the results.
   
   
   
2. Explore the mc1.order_type_codes table. The Order_Type_Code column contains the values 1, 2, and 3, and the Order_Type column identifies the type of order associated with the code. Close the table.
   
   
   
3. At the top of the program, insert an SQL query to access mc1.order_type_codes and create a series of macro variables named Type1, Type2, and Type3. Assign the value of the Order_Type variable associated with each order type. Suppress the PROC SQL output, and add a %PUT statement to write the values of Type1, Type2, and Type3 to the log.
   
   

   Solution:

   proc sql noprint;
   select Order_Type
       into :type1-
       from mc1.order_type_codes;
   quit;
   %put &=type1 &=type2 &=type3;
   

   
   
4. Submit the PROC SQL step and the %PUT statement. Review the log. Verify that the log contains TYPE1=Retail Store TYPE2=Catalog TYPE3=Internet.
   
   
   
5. Modify the TITLE statement to use an indirect macro variable reference to the Type variable that corresponds to the value of code. Submit the entire program and review the results. Verify that the report contains the same two rows generated by the original report and that the title is High Profit Products for Retail Store Orders.
   
   

   Solution:

   %let code=1;
   title "High Profit Products for &&type&code Orders";
   proc sql number;
   select Product_ID format=z12.,
          Sum(Total_Retail_Price) format=dollar10.2 as GrossSales,
          Sum(Total_Retail_Price-CostPrice_Per_Unit)
              format=dollar10.2 as Profit
       from mc1.orders
       where Order_Type=&code 
       group by Product_ID
       having profit /grosssales > .95
       order by Profit desc;
   quit;
   title;

   
   
6. Modify the %LET statement to assign a value of 3 to the macro variable, code. Resubmit the program and review the results.
   • What is the report title?
   
   
   

   Solution:

   %let code=3;
   title "High Profit Products for &&type&code Orders";
   proc sql number;
   select Product_ID format=z12.,
          Sum(Total_Retail_Price) format=dollar10.2 as GrossSales,
          Sum(Total_Retail_Price-CostPrice_Per_Unit)
              format=dollar10.2 as Profit
       from mc1.orders
       where Order_Type=&code 
       group by Product_ID
       having profit /grosssales > .95
       order by Profit desc;
   quit;
   title;

   
   What is the report title? High Profit Products for Internet Orders
   

Level 2 Practice: Using Indirect References to Macro Variables

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.

1. Open m103p09.sas from the practices folder and review the code. Submit the program and review the results. Verify that the report is titled Customers Residing in LU and that there are three rows in the report.
   
   
   
2. At the top of the program, insert a DATA _NULL_ step to create a series of macro variables from the mc1.country_codes table. Use the value in the CountryCode column as the macro variable name, and use the value of the corresponding CountryName column as the macro variable's value. Submit the DATA _NULL_ step to create the macro variables.
   
   

   Solution:

   data _null_;
       set mc1.country_codes;
       call symputx(CountryCode,CountryName);
   run;

   
   
3. Modify the TITLE statement to include the country name based on the value of the code macro variable. Verify that the title is Customers Residing in Luxembourg.
   
   

   Solution:

   %let code=LU;
   title "Customers Residing in &&&code";
   proc print data=mc1.customers;
       id ID;
       var Name Age_Group;
       where Country="&code";
   run;
   title;

   
   
4. Modify the %LET statement to assign a value of ZA to the macro variable, code. Submit the program and review the results.
   • How many customers are from ZA, and what is the country name?
   
   
   

   Solution:

   %let code=ZA;
   title "Customers Residing in &&&code";
   proc print data=mc1.customers;
       id ID;
       var Name Age_Group;
       where Country="&code";
   run;
   title;

   
   How many customers are from ZA, and what is the country name? There are seven customers from South Africa.
   

Level 1 Practice: Defining and Using a Macro with Parameters

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.

1. Open m104p01.sas from the practices folder. Review and submit the program. Verify that it generates a report for Gold customers that contains 680 rows.
   
   
   
2. Convert the code to a macro named Customers that accepts a positional parameter named Type.
   • Submit the macro definition and review the log to verify that the macro compiled successfully.
   • Call the Customers macro with the parameter value Gold. Verify that the title is Gold Customers and that there are 680 rows in the report.
   • Call %Customers again with the parameter value High Activity. Verify that no rows were selected due to a case difference.
   
   

   Solution:

   %macro customers(type); 
   title "&type Customers"; 
   proc sql number;
   select Name, Age_Group, Type 
       from mc1.customers 
       where Type contains "&type"; 
   quit; 
   title;
   %mend customers;
   
   %customers(Gold)
   %customers(High Activity)

   
   
3. Modify the macro definition.
   • Convert the value of the parameter Type to uppercase.
   • Modify the WHERE clause to make the string comparison case insensitive.
   • Submit the macro definition and verify that it compiled correctly.
   • Call the macro with the parameter value High Activity. Verify that the title is HIGH ACTIVITY Customers and that the report contains 461 rows.
   
   

   Solution:

   %macro customers(type); 
   %let type=%upcase(&type);
   title "&type Customers"; 
   proc sql number;
   select Name, Age_Group, Type 
       from mc1.customers 
       where upcase(Type) contains "&type"; 
   quit; 
   title;
   %mend customers;
   
   %customers(High Activity)

   
   
4. Change the positional parameter to a keyword parameter with a default value of inactive.
   • Call %Customers with the value high activity for the keyword parameter. Verify that the title is HIGH ACTIVITY Customers and that the report contains 461 rows.
   • Call %Customers without passing a parameter value. Verify that title is INACTIVE Customers.
   • How many rows are in the final report?
   
   

   Solution:

   %macro customers(type=inactive); 
   %let type=%upcase(&type);
   title "&type Customers"; 
   proc sql number;
   select Name, Age_Group, Type 
       from mc1.customers 
       where upcase(Type) contains "&type"; 
   quit; 
   title;
   %mend customers;
   
   %customers(type=high activity)
   %customers()

   
   
   How many rows are in the final report? The report contains 209 rows.
   

Level 2 Practice: Using a Macro to Generate PROC MEANS Code

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.

1. Open m104p02.sas from the practices folder. Submit the program and review the results. Verify that the mean value of Total_Retail_Price for orders submitted by customer 51 is 314.74.
   
   
   
2. Convert the code to a macro named Orderstats that accepts the keyword parameters listed below. Set default values for all parameters so that the code reproduces the original report when called without parameter values. Modify the PROC MEANS code to reference the parameters.
   • var for the VAR statement variable list
   • class for the CLASS statement variable list
   • stats for the statistics to be calculated
   • decimals for the MAXDEC= value
   
   

   Solution:

   %macro orderstats(var=Total_Retail_Price, class=Customer_ID,
   				 stats=mean, decimals=2); 
   options nolabel; 
   title 'Order Stats'; 
   proc means data=mc1.orders maxdec=&decimals &stats; 
       var &var;
       class &class; 
   run; 
   title;
   options label;
   %mend orderstats;

   
   
3. Call the Orderstats macro without passing parameter values. Verify that the mean value of Total_Retail_Price for Customer 51 is the same as in step 1.
   
   

   Solution:

   %orderstats()

   
   
4. Call the macro again, with the parameter values listed below.
   • var=CostPrice_Per_Unit
   • decimals=0
   • stats=mean median
   • class=order_type
   What is the median value for Order_type 3?
   
   

   Solution:

   %orderstats(var=CostPrice_Per_Unit, decimals=0,
               stats=mean median, class=order_type)

   
   
   What is the median value for Order_type 3 (internet orders)? The median is 26.
   

Challenge Practice: Using Parameters That Contain Special Characters

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.

1. Open m104p03.sas from the practices folder. Review and submit the program. The macro completes compilation without errors, but six warning messages are displayed because the parameter contains a special character. Macro quoting functions are needed to eliminate the unresolved references.
   
   
   
2. Modify the macro call to protect the parameter value at compile time and call the macro again. Verify that four warning messages are displayed in the log.
   
   

   Solution:

   %prodlist(%nrstr(clothes&shoes))

   
   
3. You protected the special character during compilation, but during execution, the resolved value of prodline contains an unprotected special character. The Q macro quoting functions mask special characters in the result and return quoted text. The function names begin with %Q (for example, %QSCAN).
   
   Modify the macro definition to use Q functions where needed to eliminate all warning messages from the log. Submit the modified code and verify that there are no error or warning messages in the log.
   
   

   Solution:

   %macro prodlist(prodline);
   %let prodline=%qupcase(&prodline);
   title "Listing of %qsysfunc(propcase(&prodline, %str( &)))";
   proc print data=mc1.products;
       where upcase(Product_Line)="&prodline";
       var Product_ID Product_name Product_Line;
   run;
   title;
   %mend prodlist;
   
   %prodlist(%nrstr(clothes&shoes))

   
   

Level 1 Practice: Understanding Symbol Tables

Without submitting the programs, identify in which symbol table the macro variable Zip is located. Assume that each example is submitted in its own SAS session.

1. %let Zip=27513; 
   %macro whereis; 
       %let state=%sysfunc(zipnamel(&Zip));
       %put The Zip Code &Zip is in &state;
   %mend whereis;
   
   %whereis

   
   
   

   Solution:

   The %LET statement is outside the macro definition, so Zip is stored in the global symbol table.
   

2. %macro whereis;
       %let Zip=10312; 
       %let state=%sysfunc(zipnamel(&Zip));
       %put The Zip Code &Zip is in &state;
   %mend whereis;
   
   %whereis

   
   
   

   Solution:

   The %LET statement is inside the macro definition, so Zip is stored in the local symbol table.
   

3. %macro whereis(Zip); 
       %let state=%sysfunc(zipnamel(&Zip));
       %put The Zip Code &Zip is in &state;
   %mend whereis;
   
   %whereis(91219)

   
   
   

   Solution:

   The %whereis macro is defined with a parameter, so a local symbol table is created, and Zip is stored in this local table.
   

Level 2 Practice: Controlling Macro Variable Scope

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.

1. Open m104p05.sas from the practices folder. Notice that macro variables are created inside the Scope macro using %LET statements. Highlight the code in Section 1, run the selection, and review the log.
   
   • In which symbol table were the macro variables created?
   
   Note: The log labels global macro variables as GLOBAL and local macro variables with the name of the macro.
   
   

   Solution:

    /* Section 1: Macro variables created with %LET */
   %macro scope;
   %let stormtype1=Some damage;
   %let stormtype2=Extensive damage;
   %let stormtype3=Devastating damage;
   %let stormtype4=Catastrophic damage;
   %let stormtype5=Widespread catastrophic damage;
   %put _user_;
   %mend scope;
   
   %scope

   
   
   In which symbol table were the macro variables created? Scope's local symbol table
   
2. Find Section 2 in the program. Notice that macro variables are created inside the Scope macro using the SQL INTO clause. Highlight the code in Section 2, run the selection, and review the log.
   • In which symbol table were the macro variables created?
   
   

   Solution:

    /* Section 2: Macro variables created with SQL INTO */ 
   %macro scope;
   proc sql noprint;
   select damage into :stormtype1-
   	from mc1.storm_cat
   	order by category;
   quit;
   
   %let num = &sqlobs;
   %put _user_;
   %mend scope;
   
   %scope

   
   
   In which symbol table were the macro variables created? Scope's local symbol table
   
3. Find Section 3 in the program. Notice that macro variables are created inside the Scope macro using the CALL SYMPUTX routine in a DATA step. Highlight the code in Section 3, being sure to include the %SYMDEL statement. Run the selection and review the log.
   • In which symbol table were the macro variables created?
   
   

   Solution:

   /* Section 3: Macro variables created with CALL SYMPUTX */ 
   %macro scope;
   data _null_;
   	set mc1.storm_cat end=last;
   	call symputx(cat('stormtype',_n_),Damage);
   	if last=1 then
   		call symputx('num',_n_);
   run;
   %put _user_;
   %mend scope;
   
   %scope
   
   %symdel stormtype1 stormtype2 stormtype3 stormtype4 stormtype5 num;

   
   
   In which symbol table were the macro variables created? Global symbol table
   
   The Scope macro doesn't accept parameters or include any %LET statements, so no local symbol table is created. Because there is no local symbol table, CALL SYMPUTX creates the macro variables in the global symbol table.
   
4. Add a %LOCAL statement before the DATA step to define a macro variable named x. Highlight the code in Section 3, run the selection, and review the log.
   • In which symbol table were the macro variables created?
   
   

   Solution:

    /* Section 3: Macro variables created with CALL SYMPUTX */
   %macro scope;
   %local x;
   data _null_;
   	set mc1.storm_cat end=last;
   	call symputx(cat('stormtype',_n_),Damage);
   	if last=1 then
   		call symputx('num',_n_);
   run;
   %put _user_;
   %mend scope;
   
   %scope
   
   %symdel stormtype1 stormtype2 stormtype3 stormtype4 stormtype5 num;

   
   
   In which symbol table were the macro variables created? Scope's local symbol table
   
5. Delete the %LOCAL statement and modify the CALL SYMPUTX statement to specify macro variables should be created in the local symbol table. Highlight the code in Section 3, run the selection, and review the log.
   • In which symbol table were the macro variables created?
   
   Note: The %SYMDEL statement generates warnings because the macro variables listed are not currently in the global symbol table.
   
   

   Solution:

    /* Section 3: Macro variables created with CALL SYMPUTX */
   %macro scope;
   data _null_;
   	set mc1.storm_cat end=last;
   	call symputx(cat('stormtype',_n_),Damage,"L");
   	if last=1 then
   		call symputx('num',_n_,"L");
   run;
   %put _user_;
   %mend scope;
   
   %scope
   
   %symdel stormtype1 stormtype2 stormtype3 stormtype4 stormtype5 num;

   
   
   In which symbol table were the macro variables created? Scope's local symbol table
   

Challenge Practice: Debugging Macro Variable Scope Issues

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.

1. Open m104p06.sas from the practices folder. Review and submit the program. Why does it fail?
   
   

   Solution:

   A local symbol table is created by the number_of_rows macro because it includes a parameter. Therefore, the CALL SYMPUTX routine writes the Numrows macro variable to the local symbol table, and it is deleted when the macro finishes execution.
   
2. Fix the program and resubmit.
   • What is the value of Numrows?
   
   

   Solution:

   %macro number_of_rows(table);
   options nonotes;
   data _null_;
       call symputx("numrows", number, "g");
       stop;
       set &table nobs=number;
   run;
   %mend number_of_rows;
   
   %number_of_rows(mc1.customers)
   %put mc1.customers has &numrows rows.;
   
   %symdel numrows;
   options notes;

   
   
   What is the value of Numrows? 1800
   

Level 1 Practice: Using Macro Conditional Processing to Choose Which SAS Statements Are Generated

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.

1. Open m104p07.sas from the practices folder.
   
   
   
2. Modify the CustomerList macro to test the country parameter. If the value is null, insert the following statements into the PROC PRINT step:
   

   title "All Customers";
   var ID Name Country Type Age_Group;

   
   If the value is not null, insert the following statements into the PROC PRINT step:
   

   title "Customers from Country: &country";
   where Country="&country";
   var ID Name Type Age_Group;

   
   
   

   Solution:

   %macro customerlist(country);
   proc print data=mc1.customers noobs;
       %if &country= %then %do;
           title "All Customers";
           var ID Name Country Type Age_Group;
       %end;
       %else %do;
           title "Customers from Country: &country";
           where Country="&country";
           var ID Name Type Age_Group;
       %end;
   run;
   %mend customerlist;

   
   
3. Submit the macro definition and call the macro using a null value. Verify that the title is All Customers and that 1800 rows were included in the report.
   
   

   Solution:

   %customerlist()

   
   
4. Submit the macro call again with a value of AU for the country parameter.
   
   • What is the report title?
   • How many customers are included in the report?
   
   

   Solution:

   %customerlist(AU)

   
   
   • What is the report title? Customers from Country: AU
   • How many customers are included in the report? 90
   
   

Level 2 Practice: Using Macro Conditional Processing in Open Code

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.

1. Open m104p08.sas from the practices folder. Run the program and verify that a table named Profit was created in the work library, and that a bar chart was produced.
   
   
   
2. Create the table in a permanent library.
   • Add a LIBNAME statement to create a library named Orion that points to the practices folder in the course files. Use the path "&path/practices".
   • Add a %PUT statement to write the value of the automatic macro variable syslibrc to the log. If the LIBNAME statement ran successfully, the value of syslibrc is zero. Otherwise, it is a value other than zero.
   Run the LIBNAME and %PUT statements.
   
   

   Solution:

   libname orion "&path/practices";
   %put &=syslibrc;

   
   
3. Modify the program.
   • Modify the CREATE TABLE statement to write the Profit table to the Orion library.
   • Add macro conditional statements.
     • If syslibrc is not equal to zero, then write a custom error message to the log indicating the rest of the program will not execute because of the failure of the LIBNAME statement.
     • If syslibrc is equal to zero, run the PROC SQL and SGPLOT steps.

   Solution:

   libname orion "&path/practices";
   %put &=syslibrc;
   
   %if &syslibrc ne 0 %then %do;
       %put ERROR: Because of the error in the LIBNAME statement, the rest of the program will not execute.;
   %end;
   
   %else %do;
       proc sql;
       create table orion.profit as
       select o.product_id, Product_Name, Product_Category,  
              (Total_Retail_Price-(CostPrice_per_Unit*Quantity)) 
   	          as Profit format=dollar12.2 
           from mc1.orders as o inner join mc1.products as p 
           on o.product_id=p.product_id;
       quit;
   	
       proc sgplot data=orion.profit noautolegend;
           hbar Product_Category / response=Profit fillType=gradient 
                                   categoryorder=respdesc;
       run;
   %end;

   
   
4. Test the program with and without an error in the LIBNAME statement. If the graph is created successfully, which Product_Category value has the highest sum of Profit?
   
   

   Solution:

   If the graph is created successfully, which Product_Category value has the highest sum of Profit? Outdoors
   

Challenge Practice: Using Macro Conditional Processing to Test Parameter Values

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.

1. Open m104p09.sas from the practices folder. The SalesSummary macro accepts a single parameter, ot, that indicates the OrderType. Valid values for OrderType are 1, 2, and 3.
   
   Submit the macro definition and then call it three times to verify that the it runs successfully when ot is 1, 2, or 3.
   
   

   Solution:

   %SalesSummary(1)
   %SalesSummary(2)
   %SalesSummary(3)

   
   
2. Use conditional macro statements to test the value of ot. If it is not equal to 1, 2, or 3, then write the following custom error message to the log. Otherwise, run the rest of the macro program.
   

   ERROR: is not a valid order type. Order type values include 1 (Retail), 2 (Catalog), and 3 (Internet).

   
   

   Solution:

   %macro SalesSummary(ot) / minoperator;
   %if not (&ot in 1 2 3) %then %do;
       %put ERROR: &ot is not a valid order type. Order type values include 1
   (Retail), 2 (Catalog), and 3 (Internet).;
   %end;
   
   %else %do;
       %if &ot=1 %then %let otdesc=Retail;
       %else %if &ot=2 %then %let otdesc=Catalog;
       %else %if &ot=3 %then %let otdesc=Internet;
   	
       title "Total Retail Price by Year for &OTDesc Sales";
       proc means data=mc1.orders sum mean median maxdec=0;
           where Order_Type=&ot;
           var Total_Retail_Price;
           class Order_Date;
           format Order_Date year4.;
       run;	
   %end;
   
   %mend SalesSummary;

   
   
3. Call SalesSummary with a parameter value of 4. Confirm that the error message is displayed.
   
   

   Solution:

   %SalesSummary(4)
   
   73   %SalesSummary(4)
   ERROR: 4 is not a valid order type. Order type values include 1 (Retail), 
   2 (Catalog), and 3 (Internet).

   
   
4. Call SalesSummary with a parameter value of 1. Confirm that a report is generated for retail sales.
   
   

   Solution:

   %SalesSummary(1)
   
   73   %SalesSummary(1)
   NOTE: There were 11133 observations read from the data set MC1.ORDERS.
          WHERE Order_Type=1;

   
   

Level 1 Practice: Using Iterative Processing and Indirect Referencing

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.

1. Open m104p10.sas from the practices folder. Review and submit the program. Verify that it generates a PROC MEANS report for Type 1 orders.
   
   
   
2. Convert the code to a macro named Orders that uses a %DO loop to generate a separate PROC MEANS step for order types 1, 2, and 3. Add a call to the Orders macro. Submit the program and verify that it generates three PROC MEANS reports, one for each order type. Confirm that the mean of Total_Retail_Price for type 2 orders is 162.43.
   
   

   Solution:

   %macro orders;
   %do i=1 %to 3;
       title "Order Type: &i";
       proc means data=mc1.orders sum mean maxdec=2;
           where Order_Type=&i;
           var Total_Retail_Price CostPrice_Per_Unit;
       run;
   %end;
   %mend orders;
   
   %orders

   
   
3. Modify the program.
   • Insert a DATA _NULL_ step after the %MACRO statement.
   • Create a series of macro variables, type1 to typen, where n is the number of order types found in the mc1.order_type_codes table.
   • For each macro variable name, concatenate the prefix type with the value of order_type_code.
   • Assign the corresponding value of Order_Type to the macro variable.
   • Create a macro variable named numTypes and assign it the number of type macro variables created. Hint: Use the END= option in the SET statement.
   • Use numTypes as the stop value for the %DO loop.
   • Use an indirect reference to include the order type in the title as shown below.

     Order Type: Retail Store

   
   

   Solution:

   %macro orders;
   data _null_;
       set mc1.order_type_codes end=last;
       call symputx(cats("type",Order_type_code),Order_type);         
       if last=1 then call symputx("numTypes", _n_);
   run;
   %do i=1 %to &numTypes;
       title "Order Type: &&type&i";
       proc means data=mc1.orders sum mean maxdec=2;
           where Order_Type=&i;
           var Total_Retail_Price CostPrice_Per_Unit;
       run;
   %end;
   %mend orders;
   
   %orders

   
   
4. Submit the program. What is the title of the last report?
   
   

   Solution:

   What is the title of the last report? Order Type: Internet
   

Level 2 Practice: Using a %DO %UNTIL Loop

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.

1. Open m104p11.sas from the practices folder. Review and submit the program. Verify that the maximum wind speed for the 2015 season was 213 MPH.
   
   
   
2. Modify the Storms macro to accept a space-separated list of years. Use a %DO %UNTIL loop and the %SCAN macro function to generate a report for each year in the list. Submit the macro definition and verify that it completed compilation without errors.
   
   

   Solution:

   %macro storms(years);
   %let i=1;
   %let yr=%scan(&years,&i);
   %do %until(&yr eq );
       title "&yr Storms";
       proc means data=mc1.storm_final n min mean max
                  maxdec=0;
           var MaxWindMPH MinPressure;
           where season=&yr;
       run;
       %let i=%eval(&i+1);
       %let yr=%scan(&years,&i);
   %end;
   title;
   %mend storms;

   
   
3. Call %Storms to generate a report for the 2011 and 2014 seasons as shown below.

   %storms(2011 2014)

   
   
   • What was the maximum wind speed in each year?
   
   

   Solution:

   What was the maximum wind speed in each year? The maximum wind speed was 155 MPH in 2011 and 161 MPH in 2014.
   

Challenge Practice: Using Iterative Processing in One Macro to Call Another Macro

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.

1. Open m104p12.sas from the practices folder. Review and submit the program. The Print macro accepts three parameters and generates a PROC PRINT step that lists the requested number of rows from the table specified in the parameter values. The macro call requests the first 10 rows of mc1.orders. Submit the code in Part a and review the results.
   
   
   
2. Part b contains starter code for the Printlib macro, which accepts a libref and the number of rows as parameters. The PROC SQL step creates a series of macro variables that contain the names of the tables in the specified library.
   
   Complete the Printlib macro by adding an iterative %DO loop that calls the Print macro repetitively to print the first numrows rows of every table in the specified library. If the numrows parameter is not specified, the Print macro should use the default value.
   
   

   Solution:

   %macro print(lib=mc1,tbl=storm_ocean_codes,rows=3);
   title "First &rows Rows from Table: &lib..&tbl";
   proc print data=&lib..&tbl(obs=&rows);
   run;
   title;
   %mend print;
   
   %macro printlib(lib,numrows);
   %let lib=%upcase(&lib);
   proc sql noprint;
   select memname 
       into :tbl1- 
       from dictionary.tables
       where libname="&lib";
   quit;
   /* add the %DO loop here */
   %do i=1 %to &sqlobs;
       %if &numrows= %then 
          %print(lib=&lib,tbl=&&tbl&i);
       %else
          %print(lib=&lib,tbl=&&tbl&i,rows=&numrows);
   %end;
   %mend printlib;

   
   
3. Call %Printlib to list the first 10 rows of every table in the mc1 library.
   • What is the name of table in the last PROC PRINT report?
   • How many rows are in the last report?
   
   

   Solution:

   %printlib(mc1,10)

   
   
   • What is the name of table in the last PROC PRINT report? mc1.storm_type_codes
   • How many rows are in the last report? Five
   
   
4. Call %Printlib to list every table in the mc1 library. Do not specify a value for numrows.
   • How many rows are displayed in each report?
   
   

   Solution:

   %printlib(mc1)

   
   
   • How many rows are displayed in each report? Three
   
   

Level 1 Practice: Generating Data-Dependent Listing Reports

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.

1. Open m105p01.sas from the practices folder. The program submits a PROC PRINT step to generate a report for the requested customer type in the mc1.customers table. Submit the program and verify that a report listing Gold high activity customers is produced.
   
   
   
2. Include the code in a macro named GroupList with two parameters, Tab for the input table and Col for the selected column. This macro generalizes the starter program so that a PROC PRINT report is generated for each unique value of the specified column.
   
   
   
   • Use a PROC SQL step to select the distinct uppercase values of the Col parameter. Load the values into a numbered series of macro variables starting with Val1. Read the column from the table specified by the Tab parameter.
   
   
   • Add a macro %DO loop to repeat the TITLE statement and PROC PRINT step once for each Valn macro variable created. Note: Use the Sqlobs macro variable created automatically by the PROC SQL step to provide the stop value for the loop.
   
   
   • Use an indirect macro variable reference to replace GOLD HIGH ACTIVITY with Val1 for the first %DO loop iteration, Val2 for the second iteration, and so on.
   
   

   Solution:

   %macro grouplist(tab,col);
   proc sql noprint;
   select distinct upcase(&col)
       into :val1-
       from &tab;
   quit;
   
   %do i=1 %to &sqlobs;
   title "Group: &&val&i";
   proc print data=&tab;
       where upcase(&col)="&&val&i";
   run;
   %end;
   %mend grouplist;

   
   
3. Call the GroupList macro with mc1.customers and Type as the parameter values. Confirm that a separate PROC PRINT step is executed for each distinct value of Type.
   
   

   Solution:

   %grouplist(mc1.customers, Type)

   
   
4. Call the GroupList macro with sashelp.cars and DriveTrain as the parameter values.
   • What is the value of DriveTrain for the first report generated?
   
   

   Solution:

   %grouplist(sashelp.cars, DriveTrain)

   
   What is the value of DriveTrain for the first report generated? ALL
   

Level 2 Practice: Exporting Data to Separate Worksheets in Microsoft Excel

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.

1. Open m105p02.sas from the practices folder. Review and submit the program. Open the Excel file to confirm storms from the NA basin are included.
   • SAS Studio: In the Files and Folders section of the navigation pane, navigate to the course data folder, select the NAStorms.xlsx file, and click the Download icon. Close the Excel file after viewing it.
   • Enterprise Guide: In the Servers window, expand Local > Files and navigate to the course data folder. Double-click the NAStorms.xlsx file. Close the Excel file after viewing it.
   
   
   
2. Create a macro named BasinXLS that writes storms for each distinct value of Basin to a different worksheet in an Excel workbook named BasinStorms.xlsx. Include the following logic in the macro program:
   • Use the automatic macro variable Syslibrc to verify whether the LIBNAME statement runs successfully (SYSLIBRC=0). If it does not run successfully (SYSLIBRC≠0), write a custom error message to the log and terminate macro execution.
   • Create a numbered series of macro variables for each value of Basin and BasinName in the mc1.storm_basin_codes table. Remove all spaces from the BasinName values before assigning them to macro variables.
   • Use a macro %DO loop to repeat the DATA step for each value of Basin.
   
   

   Solution:

   %macro basinxls;
   libname storm xlsx "&path/basinstorms.xlsx";
   %if &syslibrc ne 0 %then %do;
       %put ERROR: BasinStorms.xlsx was not successfully assigned. Check the path and filename.;
   %end;
   
   %else %do;
       proc sql noprint;
       select Basin, compress(BasinName)
           into :basin1-, :basinname1-
   	   from mc1.storm_basin_codes;
       quit;
   	
       %do i=1 %to &sqlobs;
       data storm.&&basinname&i.._Storms;
           set mc1.storm_final;
           where Basin="&&basin&i";
       run;
       %end;
   %end;
   libname storm clear;
   %mend basinxls;

   
   
3. Call the %BasinXLS macro and open the BasinStorms.xlsx file. Confirm that six worksheets are included.
   • What is the name of the first storm listed in the SouthIndian_Storms worksheet?
   
   

   Solution:

   %basinxls

   
   What is the name of the first storm listed in the SouthIndian_Storms worksheet? ALFRED
   

Challenge Practice: Analyzing All Columns from a Table

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.

1. Open m105p03.sas from the practices folder. Review and submit the program. The SQL query generates a report based on the dictionary.columns table that includes a list of all columns and attributes from the selected table. The SGPLOT step creates a histogram to visualize the distribution of the numeric column, MSRP. The SGPIE step creates a donut chart to visualize the distribution of the character column, Type.
   
   
   
2. Create a macro named ColumnReport with one parameter, Tab. Add the following logic to the macro to create a histogram for all numeric columns in the selected table and a donut chart for all the character columns:
   • Convert the Tab parameter value to uppercase.
   • After the first SQL query, add another SQL step to create a series of numbered macro variables that will store the column values for Name and Type.
   • Use a %DO loop to execute either the SGPLOT or SGPIE step for each column in the input table. If the column type is num, then run the SGPLOT step to create the histogram. If the column type is char, then run the SGPIE step to create the donut chart.
   • Add a title for each graph: Distribution of <columnname>.
   
   

   Solution:

   %macro columnreport(tab);
   
   %let tab=%upcase(&tab);
   
   title "Analysis of Columns from &tab Table";
   proc sql;
   select Name, Label, Type, Length, Format
       from dictionary.columns
       where libname="%scan(&tab,1)" and 
   		 memname="%scan(&tab,2)";
   quit;
    
   proc sql noprint;
   select Name, Type, Label
       into :col1-, :type1-, :label1-
       from dictionary.columns
       where libname="%scan(&tab,1)" and 
   	     memname="%scan(&tab,2)";
    quit;
   
   %do i=1 %to &sqlobs;
   title1 "Distribution of &&label&i";
   title2 "Column Name: &&col&i";
       %if &&Type&i=num %then %do; 
           proc sgplot data=&tab;
               histogram &&col&i;
           run;
       %end;
   
       %else %do;
           proc sgpie data=&tab;
               donut &&col&i;
           run;
       %end;
   %end;
   %mend columnreport;

   
   
3. Call the %ColumnReport macro with mc1.storm_final as the parameter value. Verify that the appropriate charts are created for each column.
   
   Note: Character columns with many unique values, such as Name, are grouped into a category labeled Other.
   
   Note: You can ignore warnings regarding the font.
   
   

   Solution:

   %columnreport(mc1.storm_final)

   
   
4. Call the macro again with mc1.customers as the parameter value.
   • In the Distribution of Group chart, how many Orion Club Gold members are there?
   
   

   Solution:

   %columnreport(mc1.customers)

   
   In the Distribution of Group chart, how many Orion Club Gold members are there? 680
   

Level 1 Practice: Validating a Macro Parameter

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.

1. Open m105p04.sas from the practices folder. Review and submit the program.
   
   
   
2. Modify the macro.
   • Convert the Type parameter value to uppercase.
   • Add %IF-%THEN and %ELSE statements to validate the Type parameter.
   • The macro should submit the TITLE statement and PROC FREQ step only if the Type parameter is LOW, MEDIUM, or HIGH.
   • If the Type parameter is not correct, the macro should write this message to the log, where xxxx is the Type parameter:
     
     

     ERROR: Invalid TYPE: xxxx ERROR: Valid TYPE values are HIGH, MEDIUM, LOW

   
   

   Solution:

   %macro custtype(type) / minoperator;
       %let type=%upcase(&type);
       %if &type in HIGH MEDIUM LOW %then %do;
           title "&Type Activity Customers by Country";
           proc freq data=mc1.customers order=freq;
               where upcase(Type) contains "&type";
               tables Country;
           run;
           title;
       %end;
       %else %do;
           %put ERROR: Invalid TYPE: &type;
           %put ERROR: Valid TYPE values are HIGH, MEDIUM, LOW;
       %end;
   %mend custtype;

   
   
3. Resubmit the macro definition and call the macro using a valid and verify that a report is produced. Then call the macro with an invalid parameter value. Verify that the error message is displayed in the log.
   
   

   Solution:

   %custtype(high)
   %custtype(med)

   
   
4. Modify the macro to first test whether Type is null. If so, do not execute the TITLE statement or PROC FREQ step. Instead, the macro should write this message to the log:
   
   

   ERROR: Missing TYPE ERROR: Valid TYPE values are HIGH, MEDIUM, LOW

   
   

   Solution:

   %macro custtype(type) / minoperator;
   %if &type= %then %do;
       %put ERROR: Missing TYPE;
       %put ERROR: Valid TYPE values are HIGH, MEDIUM, LOW;
   %end;
   %else %do;
       %let type=%upcase(&type);
       %if &type in HIGH MEDIUM LOW %then %do;
           title "&Type Activity Customers by Country";
           proc freq data=mc1.customers order=freq;
               where upcase(Type) contains "&type";
               tables Country;
           run;
           title;
       %end;
       %else %do;
           %put ERROR: Invalid TYPE: &type;
           %put ERROR: Valid TYPE values are HIGH, MEDIUM, LOW;
       %end;
   %end;
   %mend custtype;

   
   
5. Resubmit the macro definition and call the macro with a null parameter value, valid values in uppercase and lowercase, and an invalid value.
   • How many low-activity customers are Italian (IT)?
   
   

   Solution:

   %custtype(HIGH)
   %custtype(low)
   %custtype(med)
   %custtype()

   
   How many low-activity customers are Italian (IT)? 60
   

Level 2 Practice: Validating Macro Parameters with Data Values

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.

1. Open m105p05.sas from the practices folder. Review and submit the program.
   
   
   
2. Use PROC SQL to create the macro variables MinSeason and MaxSeason that store the minimum and maximum values of the Season column in the mc1.storm_final table. Add a condition to verify that the Season parameter is between MinSeason and MaxSeason. If the Season parameter is outside of the valid range, write this message to the log:
   
   

   ERROR: Valid Seasons are between <minSeason> and <maxSeason>

   
   
   
3. If the Season parameter is valid, use PROC SQL to create a space-delimited list of valid Basin codes from the mc1.storm_basin_codes table. Load the list into a macro variable.
   
   
   
4. If the Basin parameter is in the list of valid codes, execute the TITLE statements and the PROC SGPLOT step.
   
   Note: Be sure to use the MINOPERATOR option in the %MACRO statement to enable the macro IN operator.
   
   
   
5. If the Basin parameter is not in the list of valid codes, write this message to the log:
   
   

   ERROR: AA is an invalid basin code. Basin codes include NA WP EP SP NI SI.

   
   

   Solution:

   %macro stormchart(basin, season) / minoperator;
   proc sql noprint;
   select min(season), max(season)
       into :minseason trimmed, :maxseason trimmed
       from mc1.storm_final;
   quit;
   
   %if &season<&minseason or &season>&maxseason %then %do;
       %put ERROR: Valid Seasons are between &minseason and &maxseason;
   %end;
   
   %else %do;
       proc sql noprint;
       select Basin
           into :basinlist separated by " "
           from mc1.storm_basin_codes;
       quit;
       %if &basin in &basinlist %then %do;
           title1 "Storm Frequency by Type";
           title2 "&basin, &season";
           proc sgplot data=mc1.storm_final noborder;
               vbar StormType / filltype=gradient;
               where Basin="&Basin" and Season=&Season;
               yaxis grid;
           run;
           title;
       %end;
       %else %do;
           %put ERROR: &basin is an invalid basin code. Basin codes include &basinlist..;
       %end;
   %end;
   %mend stormchart;

   
   
6. Test the macro with valid and invalid values for both Basin and Season.
   • How many tropical storms were there in the EP basin in 2010?
   
   

   Solution:

   %stormchart(NA,2020)
   %stormchart(EP,2010)
   %stormchart(AA,2010)

   
   How many tropical storms were there in the EP basin in 2010? Two
   

Challenge Practice: Validating a Macro Parameter with Data Values That Require Quoting Functions

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.

%SUPERQ is a helpful macro function that retrieves the value of a macro variable from the macro symbol table and quotes it immediately, preventing the macro processor from making any attempt to resolve anything that might occur in the resolved value. If the value of a macro variable could include macro triggers, operators, or special symbols, %SUPERQ is an effective alternative to resolving the macro variable with &mac-var.

Note: Do not include & in front of the macro variable name.

1. Open m105p06.sas from the practices folder. Review and submit the program.
   
   
   
2. Modify the macro so that if the Ctry parameter is null, execute a modified TITLE statement and PROC SGPLOT step that includes customers from all countries.
   
   Note: The country codes IN (India) and LT (Lithuania) are possible parameter values, which could be interpreted as the macro IN operator or the less than operator. In the %IF statement, use the %SUPERQ function to ensure that the Ctry parameter value is treated as text.
   
   
   
3. If the Ctry parameter is not null, verify that it is one of the distinct values from the Country column in the mc1.customers table. If Ctry is a valid country code, generate the chart for customers in that country.
   
   Note: Use %SUPERQ to ensure that the Ctry parameter value and the list of country codes are treated as text.
   
   
   
4. If the Ctry parameter is an invalid value, write this message to the log and substitute a list of valid country codes:
   
   

   ERROR: ZZ is an invalid country code Valid country codes include <list of country codes>

   
   

   Solution:

   %macro customerlist(ctry) / minoperator;
   %if %superq(ctry)= %then %do;
       title "All Customers";
       proc sgplot data=mc1.customers noborder;
           vbar Group / filltype=gradient stat=percent;
           yaxis grid display=(noticks noline nolabel);
       run;
   %end;
   
   %else %do;
       %let ctry=%upcase(&ctry);
       proc sql noprint;
       select distinct Country
           into :ctrylist separated by " "
           from mc1.customers;
       quit;
       %if %superq(ctry) in %superq(ctrylist) %then %do;
           title "Customers from &ctry";
           proc sgplot data=mc1.customers noborder;
               vbar Group / filltype=gradient stat=percent;
               yaxis grid display=(noticks noline nolabel);
               where Country="&ctry";
           run;	
       %end;
       %else %do;
           %put ERROR: &ctry is an invalid country code;
           %put ERROR- Valid country codes include &ctrylist;
       %end;
   %end;
   %mend customerlist;

   
   
5. Test the macro for parameter values null, AU, in, and zz.
   • What is the last valid country code listed in the log message when the Ctry parameter is zz?
   
   

   Solution:

   %customerlist()
   %customerlist(AU)
   %customerlist(in)
   %customerlist(zz)

   
   What is the last valid country code listed in the log message when the Ctry parameter is zz? ZA