Creating Data-Driven Macro Variables with PROC SQL

A SAS macro variable stores text that is substituted in your code when SAS runs the program. Macro variable execution is similar to an automatic find-and-replace. Macro variables are temporary, and they are stored in-memory, so when you exit SAS, they are deleted. However, you can create data-driven macro variables with a PROC SQL query and then use those macro variables in your SAS program, procedure, and DATA step.

Creating Data-Driven Macro Variables with the INTO Clause

The INTO Clause

You can access the macro facility in a PROC SQL step by using the INTO clause in the SELECT statement. The various forms of the INTO clause can perform a variety of functions. For example, you can create a series of macro variables, a varying number of macro variables, or a single macro variable that records a value that is the result of concatenating the unique values of an SQL variable.
The INTO clause in a SELECT statement enables you to create or update macro variables. You can create multiple macro variables from query results. If the macro variable does not exist, INTO creates it. The INTO clause in the SELECT statement can also assign the result of a query to a macro variable. However, the INTO clause can be used only in the outer query of a SELECT statement and not in a subquery. Note that the INTO clause cannot be used when you are creating a table or a view.
Syntax, INTO clause:
PROC SQL <options>;
SELECT column-1 <,...column-n>
INTO: macro-variable-specification-1 <..., :macro-variable-specification-n>
QUIT;
macro-variable-specification
names one or more macro variables to create or update. Precede each macro variable name with a colon (:).
Note: When you create or update macro variables during execution of a PROC SQL step, you might not want any output to be displayed. The PRINT | NOPRINT option specifies whether a SELECT statement's results are displayed in output. PRINT is the default setting.
Tip
Values that are assigned by the INTO clause use the BEST8. format.

Step 1: Create the Macro Variable

Suppose you are asked to find out which employees who were hired after 01JAN2015 are earning above the average salary for the company. First, create the macro variable AvgSal. 
proc sql;
   select avg(Salary)
      into:avgSal
      from certadv.payrollmaster;
quit;
The average salary value is stored in the AvgSal macro variable. If the query produces more than one row of output, the macro variable contains only the value from the first row. If the query has no rows in its output, the macro variable is not modified. If the macro variable does not yet exist, it is not created.

Step 2: Use the Macro Variable

You can use the macro variable AvgSal in titles and queries. 
title "Salaries above: &AvgSal";
proc sql;
   select EmpID, JobCode, Salary, DateofHire
      from certadv.payrollmaster
      where Salary>&avgSal and DateofHire>'01JAN2015'd;
quit;
The query above filters the data by the value of the AvgSal macro variable. The query returns rows for all employees earning above the average salary.
Output 6.1 PROC SQL Query Result
PROC SQL Query Result
Tip
You can format the macro variable value in the title by using SAS functions and formats. Here is an example: title "Salaries above: %left(%qsysfunc(putn(&AvgSal,dollar16.)))";

Displaying Macro Variable Values

You can use the %PUT statement to display the resolved macro value in the SAS log. This is a good debugging technique to ensure that the value is what you expected.
Syntax, %PUT statement:
%PUT text;
text
is any text string.
Suppose you want to view the macro variable’s value after you have used the NOPRINT option. You can use the %PUT statement to view the value.
%put avgsal=&avgSal;
The value of the macro variable is printed in the SAS log.
Log 6.1 SAS Log
avgSal=54079.62
Tip
You can also enter the following code %put &=AvgSal; and receive the same value for the macro variable as above.

Removing Leading and Trailing Blanks

When storing a value in a single macro variable, PROC SQL preserves leading or trailing blanks. You can remove leading and trailing blanks by using the TRIMMED option in the INTO clause.
Syntax, INTO clause:
PROC SQL <options>;
SELECT column-1 <,...column-n>
INTO: macro-variable-specification-1 <..., :macro-variable-specification-n> TRIMMED
QUIT;
The following example illustrates the difference between using and not using the TRIMMED option to remove leading and trailing blanks.
proc sql;
   select min(PointsEarned)
      into:MinMiles
      from certadv.frequentflyers;
quit;
%put &=MinMiles;
The following is printed to the SAS log.
MINMILES=     146
In HTML output, the spaces are automatically removed, so extra spaces are not noticeable there. If your output is sent to the SAS log or is printed in a format other than HTML, you can still see the leading and trailing blanks.
Output 6.2 PROC SQL Query Result
PROC SQL Query Result
If you included the TRIMMED option in the INTO clause, the macro variable is captured with no leading or trailing blanks.
proc sql;
   select min(PointsEarned)
      into:MinMiles trimmed
      from certadv.frequentflyers;
quit;
%put &=MinMiles;
The following is printed to the SAS log.
Log 6.2 SAS Log
MINMILES=146

Concatenating Values in Macro Variables

Sometimes, during execution of a PROC SQL step, you might want to create one macro variable that holds all values of a certain data set variable. You can use an alternate form of the INTO clause in order to take the values of a column and concatenate them into the value of one macro variable. Use the SEPARATED BY keyword to specify a character to delimit the values into a macro variable.
Syntax, INTO clause:
PROC SQL <options>;
SELECT column-1 <,...column-n>
INTO: macro-variable-1 <..., :macro-variable-n> SEPARATED BY “delimiter”
QUIT;
delimiter
is enclosed in quotation marks and specifies the character that is used as a delimiter in the value of the macro variable.
Note: This form of the INTO clause removes leading and trailing blanks from each value before performing the concatenation of values.
Suppose you want to create a macro variable named Sites that contains the names of all the training centers in your Certadv.Schedule data set. The names are separated by blanks. 
proc sql noprint;
   select distinct location into: sites separated by ' '
      from certadv.schedule;
quit;
Now, you can use the Sites macro variable in the title for your query or procedure result.
title1 "Total Revenue";
title2 "from Course Sites: &sites";
proc means data=certadv.all sum maxdec=0;
   var fee;
run;
Output 6.3 PROC MEANS Output
PROC MEANS Output

Applying a Format to Character and Numeric Variables

Suppose you have census data, Certadv.Census. You are asked to create a report that finds the states where the estimated population for 2018 is greater than the average census population in April 2010. You are also asked to find the difference between the census data and the population estimate for 2018 where the population estimate for 2018 is greater than 10 million.
The following example creates two different macro variables, CensusAvg2010 and CensusAvg2010_Format. The CensusAvg2010 macro variable has no format applied to the value of the macro variable. The CensusAvg2010_Format applies the COMMA16. format to the value of the macro variable.
proc sql noprint;
   select avg(Census_Apr2010) as No_Format, 
          avg(Census_Apr2010) as Format format=comma16.
      into:CensusAvg2010,
          :CensusAvg2010_Format
      from certadv.census;
quit;
The following example creates one macro variable, StateList, with the UPCASE23. format applied to the value of the macro variable. The value of the macro variable is filtered based on criteria in the WHERE clause and is ordered by the state name.
proc sql noprint;
   select State format=$upcase23. as State
      into:StateList separated by ', '
      from certadv.census
      where PopEst_Apr2018>&CensusAvg2010 and PopEst_Apr2018>10000000
      order by State
;
quit;
%put &=StateList;
The following is printed to the SAS log.
Log 6.3 SAS Log
ARIZONA, CALIFORNIA, FLORIDA, GEORGIA, ILLINOIS, INDIANA, MARYLAND, MASSACHUSETTS,
MICHIGAN, MISSOURI, NEW JERSEY, NEW YORK, NORTH CAROLINA, OHIO, PENNSYLVANIA, TENNESSEE, TEXAS,
VIRGINIA, WASHINGTON
The following example produces a query result using the macro variables created above. 
title "States with Population Estimates Above Census Avg: &CensusAvg2010_Format";
footnote "&StateList";
proc sql; 
   select strip(State) format=$upcase23. as State,
          Census_Apr2010 format=comma12.,
          PopEst_Apr2010 format=comma12.,
          (PopEst_Apr2018-Census_Apr2010) format=comma12. as PopChange
      from certadv.census
      where PopEst_Apr2018>&CensusAvg2010 and PopEst_Apr2018>10000000
      order by State;
quit;
title;
footnote;
Output 6.4 PROC SQL Query Result
PROC SQL Query Result
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.14.174.133