SAS logo

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


Course Code: EMC1V2, prepared date: September 13, 2023
Copyright © SAS Institute Inc., Cary, NC, USA. All rights reserved.

 

Lesson 02

SAS® Macro Language 1: Essentials
Lesson 02, Section 1 Practice

Answer the following questions.

  1. Name the possible stages involved in the SAS program flow.

    Solution:

    input stack, word scanner, compiler, and macro processor

  2. What are the four types of tokens recognized by the word scanner?

    Solution:

    name, number, literal, and special

  3. How does the word scanner detect the end of a token?

    Solution:

    a whitespace character or the beginning of a new type of token

  4. What token sequence triggers interaction with the macro processor during the tokenization process?

    Solution:

    &name or %name

  5. What macro statement writes a message to the log?

    Solution:

    %PUT

SAS® Macro Language 1: Essentials
Lesson 02, Section 2 Activity

Open m102a04.sas from the activities folder and perform the following tasks:

  1. Notice that the program includes two TITLE statements, each referencing a macro variable. At the top of the program, turn on the SYMBOLGEN option. At the bottom of the program, turn off SYMBOLGEN.

  1. Run the program and review the log and results. What is printed as the second title? Why?

  1. In the TITLE2 statement, change the single quotation marks to double quotation marks and run the program again. How do the results and the log differ?

SAS® Macro Language 1: Essentials
Lesson 02, Section 2 Activity

Open m102a05.sas from the activities folder and perform the following tasks:

  1. Modify the TITLE statement reference to &type by adding a period before the 's'.

  1. Replace the hardcoded text sashelp in the FOOTNOTE and PROC statements with a reference to the Lib macro variable (&lib).

  1. Run the program and examine the log and the error statements. Why did the program fail to run?

SAS® Macro Language 1: Essentials
Lesson 02, Section 2 Activity

  1. Open a new program and submit a %PUT statement to list all user-defined macro variables.

  1. Find the Path macro variable in the log. Path was created with a %LET statement in the libname.sas program, and it stores the location of the course files. Submit the following statements to view the value of Path. How are the messages in the log different?

    %put NOTE: &=path;
    %put ERROR- Course files are in &path;

SAS® Macro Language 1: Essentials
Lesson 02, Section 2 Activity

Open m102a07.sas from the activities folder and perform the following tasks:

  1. Review the program and notice that the DATA step creates a table named Avg_MPG. Highlight the DATA step and the %PUT statement, and run the selected code. Review the log to see all automatic macro variables stored in the global symbol table.
  1. Which macro variables that store the date and the last table created?

  1. Use macro variable references in the TITLE2 and FOOTNOTE statements to insert the table name and date into the program.

  1. Run the program. Verify that the table name is included in the second title and that the date is included in the footnote.

SAS® Macro Language 1: Essentials
Lesson 02, Section 2

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.

SAS® Macro Language 1: Essentials
Lesson 02, Section 2

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.

Lesson 03

SAS® Macro Language 1: Essentials
Lesson 03, Section 1 Activity

Open m103a01.sas from the activities folder and perform the following tasks:

  1. Examine the TITLE statement. What text appears as the title? Run the program and view the results.

  1. Add % before the UPCASE function in the TITLE statement. Run the program. What text appears as the title?

SAS® Macro Language 1: Essentials
Lesson 03, Section 1 Activity

Open m103a02.sas from the activities folder and perform the following tasks:

  1. Run the program and examine the output and the log. SAS does not have a %PROPCASE macro function, so it does not successfully resolve and the title is incorrect.
  1. Modify the TITLE statement to use the %SYSFUNC macro function in combination with the PROPCASE function.

  1. Run the program and confirm that the title is correct.

SAS® Macro Language 1: Essentials
Lesson 03, Section 1 Activity

Open m103a03.sas from the activities folder and perform the following tasks:

  1. The intent of the %SCAN function is to return the city from the Location macro variable using only the comma as a delimiter. Run the program and examine the log. Why does the program fail?

  1. Use %STR to mask the comma in the value assigned to Location. What is the result and why?

  1. Use %STR to mask the comma delimiter in the call to %SCAN so that the value of the City macro variable is Buenos Aires.

SAS® Macro Language 1: Essentials
Lesson 03, Section 1

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_;
    

SAS® Macro Language 1: Essentials
Lesson 03, Section 1

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.

SAS® Macro Language 1: Essentials
Lesson 03, Section 1

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);
    

SAS® Macro Language 1: Essentials
Lesson 03, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 03, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 03, Section 3 Activity

Open m103a04.sas from the activities folder and perform the following tasks:

  1. Run the program with Acura as the value of Make. First, examine the output data and confirm that the value of HybridFlag is missing for the last row because there are no hybrid cars. Second, confirm that the footnote for the report is correct.
  1. Modify the %LET statement to assign Honda as the value of the Make macro variable. Run the program and view the output data. Confirm that the value of HybridFlag is 1 for the last row.
  1. Examine the report. Is the footnote correct?

SAS® Macro Language 1: Essentials
Lesson 03, Section 3 Activity

Open m103a06.sas from the activities folder and perform the following tasks:

  1. Submit the %LET statement and PROC MEANS step. Examine the output data.
  1. Complete the CALL SYMPUTX statement to create a macro variable named AvgMSRP and load the value of the Mean column from the CarsStat table.

  1. Run the completed program. What is the value of the second title? Which DATA step function can convert a number to a formatted character string?

SAS® Macro Language 1: Essentials
Lesson 03, Section 3

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

SAS® Macro Language 1: Essentials
Lesson 03, Section 3

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)

SAS® Macro Language 1: Essentials
Lesson 03, Section 4 Activity

Open m103a07.sas from the activities folder and perform the following tasks:

  1. Notice that the wind values have been replaced with &wind&cat. Run the program and review the log.

  1. Change &wind&cat to &&wind&cat. Run the program and review the log. Does the program run successfully?

SAS® Macro Language 1: Essentials
Lesson 03, Section 4

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

SAS® Macro Language 1: Essentials
Lesson 03, Section 4

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.

Lesson 04

SAS® Macro Language 1: Essentials
Lesson 04, Section 1 Activity

Open m104a01.sas from the activities folder and perform the following tasks:

  1. Notice that there is a missing semicolon after the PROC PRINT statement. Run the program and confirm that an error is generated in the log. Do not correct the error.

  1. Modify the code to create a macro program named PrintTable. Run the program and verify that there are no notes, warnings, or errors in the log.

  1. Add an OPTIONS statement at the start of the program to set the MCOMPILENOTE=ALL option.

  1. Submit the program. Did the PrintTable macro compile without errors?

SAS® Macro Language 1: Essentials
Lesson 04, Section 1 Activity

Open m104a02.sas from the activities folder and perform the following tasks:

  1. Run the program and verify in the log that the PrintTable macro compiled successfully.

  1. Add a line of code at the bottom of the program to call the PrintTable macro.

  1. Submit the macro call. Based on only what you see in the log, can you tell where in the SAS program the error occurred?

SAS® Macro Language 1: Essentials
Lesson 04, Section 1 Activity

Open m104a03.sas from the activities folder and perform the following tasks:

  1. Modify the %MACRO statement to use keyword parameters. Assign default values of NA to Basin, 2016 to Season, and 20 to MaxWind. Run the program and verify that the macro compiles successfully.

  1. Call the %StormChart macro with no parameter values provided. Confirm in the log that the default parameter values are used.

  1. Call the %StormChart macro with Season as 2015 and Basin as EP. View the log. Is the table subset by MaxWind even though it is not included as a parameter in the macro call?

SAS® Macro Language 1: Essentials
Lesson 04, Section 1

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.

SAS® Macro Language 1: Essentials
Lesson 04, Section 1

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.

SAS® Macro Language 1: Essentials
Lesson 04, Section 1

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))

SAS® Macro Language 1: Essentials
Lesson 04, Section 2 Activity

Open m104a04.sas from the activities folder and perform the following tasks:

  1. Notice that the %LET statement outside the Test macro definition sets the value of X to OutsideMacro. Then the %LET statement inside the macro definition sets the value of X to InsideMacro.

  1. Submit the program and examine the log. What is the value of X before, during, and after the Test macro execution?

SAS® Macro Language 1: Essentials
Lesson 04, Section 2 Activity

Open m104a06.sas from the activities folder and perform the following tasks:

  1. Review the program. Notice that the Test macro includes a parameter named X. X is assigned different values before and during the macro call.
  1. Submit the program and examine the log to view the value of X before, during, and after macro execution.
  1. What is the scope of the X parameter in the Test macro?

  1. Add a %GLOBAL statement before the %PUT statement in the Test macro definition to explicitly declare X as global scope. Does the program run successfully?

SAS® Macro Language 1: Essentials
Lesson 04, Section 2

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.

SAS® Macro Language 1: Essentials
Lesson 04, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 04, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 04, Section 3 Activity

Open m104a07.sas from the activities folder and perform the following tasks:

  1. Notice that there is a missing semicolon after the DATA statement, which causes a syntax error. If there is a syntax error in the first DATA step, then the %PUT statement should execute and write a custom error message to the log, and the remaining PROC steps should not execute.

  1. Run the program and look at the log. Notice that although there was an error in the DATA step, SAS still attempted to execute PROC PRINT and PROC SGPLOT.

  1. Identify the syntax error in the macro %IF statement. Fix the error and run the program again to confirm that the %PUT statement is executed.

SAS® Macro Language 1: Essentials
Lesson 04, Section 3 Activity

Open m104a08.sas from the activities folder and perform the following tasks:

  1. Review the program and notice that different DATA steps run, depending on the value of the Loc parameter. The avgfuel macro call at the end of the program assigns Europe to the Loc parameter.
  1. Run the program and view the log. Notice the MPRINT messages displaying the final program that was compiled and executed.
  1. Call the avgfuel macro with usa as the parameter value, by submitting only the macro call. View the log. Which DATA step was compiled and executed?

  1. Submit the macro call again, but this time include the OPTIONS statements to enable and disable MPRINT and MLOGIC. View the log. Which DATA step was compiled and executed?

SAS® Macro Language 1: Essentials
Lesson 04, Section 3

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

SAS® Macro Language 1: Essentials
Lesson 04, Section 3

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

SAS® Macro Language 1: Essentials
Lesson 04, Section 3

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;

SAS® Macro Language 1: Essentials
Lesson 04, Section 4 Activity

Open m104a09.sas from the activities folder and perform the following tasks:

  1. Run the program and examine the log. Identify and correct the syntax error and rerun the program. Verify that the macro compiled successfully.

  1. Call the %AllCylinders macro with a start value of 3 and stop value of 12.

  1. Examine the reports and log. Why are there some values between 3 and 12 that did not produce reports?

SAS® Macro Language 1: Essentials
Lesson 04, Section 4

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

SAS® Macro Language 1: Essentials
Lesson 04, Section 4

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.

SAS® Macro Language 1: Essentials
Lesson 04, Section 4

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

Lesson 05

SAS® Macro Language 1: Essentials
Lesson 05, Section 1 Activity

Open m105a01.sas from the activities folder. This program creates a macro named PrintTable in the default macro catalog and uses PROC CATALOG to list the contents of the catalog.

  1. Run the PRINTTABLE macro definition and verify that it compiles without errors. Then copy and paste this SQL step after the %PRINTTABLE macro definition.

    proc sql;
    select *
        from dictionary.catalogs
        where objname='PRINTTABLE';
    quit;

    Run the step and view the report. Note the value of Library Name and Member Name. This is the default macro catalog in your environment.
  1. Review the PROC CATALOG step. If necessary, update the CATALOG=option in the PROC CATALOG statement to reference the default macro catalog identified in Step 1.

    proc catalog catalog=work.sasmacr;
        contents;
        *contents out=macrolist(where=(name like '%TABLE%'));
    run;

    Run the step. This report includes all macros in the default macro catalog, including PRINTTABLE and its description.

  1. Uncomment the second CONTENTS statement. This statement creates a table named work.macrolist. The table includes the macros in the catalog specified in the CATALOG= option. The WHERE= data set option filters the results, including only macros with the string TABLE in their name.

    Note: The single quotation marks prevent macro resolution, so the % is treated as a wildcard for the LIKE operator rather than a macro trigger.

    contents out=macrolist(where=(name like '%TABLE%'));

    How many macros are included in the output table?

SAS® Macro Language 1: Essentials
Lesson 05, Section 1 Activity

Open m105a02.sas from the activities folder and perform the following tasks:

  1. Review the PROC CATALOG step. If necessary, update the CATALOG=option to reference the default macro catalog in your environment. Run the PROC CATALOG step. Notice in the report that the %Datatyp macro is not listed.

  1. Run the %LET and %PUT statements that call the %Datatyp macro. View the log and confirm the macro runs successfully.

  1. Highlight the PROC CATALOG step again and run the selected code. Is %Datatyp included in the report?

SAS® Macro Language 1: Essentials
Lesson 05, Section 1 Activity

Open m105a03.sas from the activities folder.

  1. Review the PROC CATALOG step. If necessary, update the CATALOG=option to reference the default macro catalog in your environment. Run the PROC CATALOG step. Is PROPCASE in the default macro catalog?

  1. Create a new program and enter the following code:

    %macro propcase(text);
       %sysfunc(propcase(&text))
    %mend propcase;
    This PropCase macro applies the PROPCASE function to the text parameter. Do not run the program, but save it as propcase.sas in the autocall folder.
  1. Open the libname.sas program. Add an OPTIONS statement to set the SASAUTOS= option to include our &path/autocall folder as an additional autocall library. Run the modified libname.sas program and save it.

  1. Return to the m105a03.sas program and uncomment the %PUT statement:
    %put Testing the PROPCASE macro: %propcase(does PROPCASE WorK?);

    Run the program. Confirm that the %PropCase executed successfully, and verify that it has been added to the default catalog.

SAS® Macro Language 1: Essentials
Lesson 05, Section 2 Activity

Open m105a04.sas from the activities folder. This program includes two SELECT statements.

  1. Run the program. The first report includes the distinct values of Origin. The second report includes a separate WHEN statement for each value of Origin.

  1. Change the value of the Col macro variable to Type. Run the program again and observer the two reports.

  1. We want to store the values returned by the first query as a space-delimited list in a macro variable named tablist, and store the values returned by the second query as a semicolon-delimited list in a macro variable named whenlist.

    What syntax do we need to add to this PROC SQL step?

SAS® Macro Language 1: Essentials
Lesson 05, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 05, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 05, Section 2

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

SAS® Macro Language 1: Essentials
Lesson 05, Section 3 Activity

Open m105a05.sas from the activities folder and perform the following tasks:

  1. Open sashelp.cars to view the table and columns.

  1. In the m105a05.sas program, select the SplitTable macro definition and submit the selected code.

  1. Highlight each %SplitTable macro call, one at a time, and submit it. Which macro calls fail and why?

SAS® Macro Language 1: Essentials
Lesson 05, Section 3 Activity

Open m105a06.sas from the activities folder and perform the following tasks:

  1. Review the program and notice that the query is reading all columns from the dictionary.columns table for sashelp.cars. Run the program and observe the information available in the generated report.
  1. Modify the query to select only the Name column and to store the returned values as a comma-delimited list in a macro variable named VarList.

  1. Add &VarList after the colon in the %PUT statement. Run the program and verify that the log lists the column names.

  1. Modify the program to examine the columns in sashelp.class. Run the program. How many columns are listed in the log?

SAS® Macro Language 1: Essentials
Lesson 05, Section 3 Activity

Open m105a07.sas from the activities folder and perform the following tasks:

  1. Submit the SplitTable macro definition and verify that it compiles without error.

  1. Call the SplitTable macro with help as the parameter value. View the log. What is causing error messages to appear in the comment?

  1. Use a macro quoting function to prevent the macro trigger from resolving in the %PUT statement. Submit the macro definition and verify that it compiles without error.

  1. Call the SplitTable macro with help as the parameter value. Confirm that the macro documentation is printed in the log.

SAS® Macro Language 1: Essentials
Lesson 05, Section 3

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

SAS® Macro Language 1: Essentials
Lesson 05, Section 3

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

SAS® Macro Language 1: Essentials
Lesson 05, Section 3

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.

%SUPERQ(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