Lesson 9, Part 3: Creating Macro Variables Using PROC SQL

Example 1 (data driven)

*Ex9_macro_vars_transfer.sas (Part 1); ods html close; options nodate nonumber nonotes nosource ps=58; proc sql noprint; select avg(weight) format=6.2 into :m_avg_wt from sashelp.class; quit; %put Old way: &m_avg_wt ; %put New way: &=m_avg_wt ;

Example 2 (Total observations)

*Ex11_one_multiple_mvars_sql.sas (Part 1);
ods html close;
options nonotes nosource nodate nonumber;
proc sql noprint;
 select count(*)
        INTO :nobs
FROM SASHELP.CARS;
quit;
%put Number of Observations = %SYSFUNC(LEFT(&nobs));
run;

                                                           The SAS System

Number of Observations = 3
                                                           The SAS System

E3969440A681A2408885998500000007

Example 3 (macro variable containing a list of values)

ods html close;
options nodate nonumber nosource;

proc sql noprint;
 select distinct region
        INTO :region_list separated by ','
        FROM SASHELP.DEMOGRAPHICS;
quit;
%put Number of regions=&sqlobs;
%put &=region_list;

                                                           The SAS System

NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1


NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

Number of regions=6
REGION_LIST=AFR,AMR,EMR,EUR,SEAR,WPR
                                                           The SAS System

E3969440A681A2408885998500000021

proc freq data=sashelp.demographics; tables region; run;

The SAS System

The FREQ Procedure

Region
region	Frequency	Percent	Cumulative Frequency	Cumulative Percent
AFR	46	23.35	46	23.35
AMR	35	17.77	81	41.12
EMR	21	10.66	102	51.78
EUR	55	27.92	157	79.70
SEAR	11	5.58	168	85.28
WPR	29	14.72	197	100.00

Example 4 (macro variable containing a list of values in quotes)

ods html close;
options nodate nonumber nonotes nosource;
proc sql noprint ;
select distinct quote(trim(region)) into :region_q_list separated by ','
from sashelp.demographics;
quit ;
%put Number of regions=&sqlobs;
%put &=region_q_list;

                                                           The SAS System

Number of regions=6
REGION_Q_LIST="AFR","AMR","EMR","EUR","SEAR","WPR"
                                                           The SAS System

E3969440A681A2408885998500000035

Example 5

ods html close;
options nodate nonumber nosource obs=max;
proc sql ;                                                                                                                              
      select quote(strip(Name))                                                                                                         
      INTO  :Starts_withC                                                                                              
    from sashelp.demographics                                                                                                           
      where Name LIKE 'C%';                                                                                                             
quit;                                                                                                                                   
%PUT &Starts_withC;

The SAS System

"CANADA"
"COSTA RICA"
"CUBA"
"CHILE"
"COLOMBIA"
"CZECH REPUBLIC"
"CROATIA"
"CAMEROON"
"CAPE VERDE"
"CENTRAL AFRICAN REP."
"CHAD"
"COMOROS"
"CONGO"
"CAMBODIA"
"CHINA"
"CYPRUS"
"COOK ISLANDS"
"CORAL SEA ISLANDS"

Note for the code in the next cell

• The macro variable follows an INTO clause and is preceded by a colon. Here, the PROC SQL creates a series of macro variables, each with its own distinct value (often referred to as a vertical list of macro variables of the form ®ION1, and so on).

• When PROC SQL is executed, a series of macro variables, including &SQLOBS is generated and placed in the most local symbol table. The value of the macro variable &SQLOBS is the number of rows processed by the SELECT statement.

• When you submit this macro, it is compiled and then stored in the default catalog work.sasmacr.

Example 6

*Ex11_one_multiple_mvars_sql.sas (Part 3);
ods html close;
options nodate nonumber nosource;
%let Put_title = List of Values into a Series of Macro Variables;
proc sql noprint;
 select distinct region
    INTO :region1-
  FROM SASHELP.DEMOGRAPHICS;
 %put Number of Rows: &sqlobs;
quit;
%macro reveal;
 %put &Put_title;
 %Do i=1 %TO &Sqlobs;
    %put &&region&i;
  %end;
%mend reveal;
%reveal

                                                           The SAS System

Number of Rows: 6
List of Values into a Series of Macro Variables
AFR
AMR
EMR
EUR
SEAR
WPR
                                                           The SAS System

E3969440A681A2408885998500000043

A Hands-on Introduction to SAS® Metadata DICTIONARY Tables and SASHELP Views

Example 7

* Ex29_symputx_sql_into.sas (Part 1);
ods html close;
options nodate nonumber nosource;
* Adapted from Simon (2017);
proc means data=sashelp.class noprint;
 var weight;
 output out=mystats mean=avweight;
 run;

data _null_;
 set mystats;
 call symputx('meanweight',avweight);
 run;

proc print data=sashelp.class noobs;
var name sex weight;
 where weight > &meanweight;
title 'Students weighing more than average weight';
title2 "Average weight:(%sysfunc(round(&meanweight, 0.01)) lbs)";
title3 '3 DATA/PROC Steps';
run;
title;

SAS Connection established. Subprocess id is 6344

Students weighing more than average weight

Average weight:(100.03 lbs)

3 DATA/PROC Steps

Name	Sex	Weight
Alfred	M	112.5
Carol	F	102.5
Henry	M	102.5
Janet	F	112.5
Mary	F	112.0
Philip	M	150.0
Robert	M	128.0
Ronald	M	133.0
William	M	112.0

Example 8 (Adding summary statistics to the observations in one step)

* Ex29_symputx_sql_into.sas (Part 2);
title 'Students weighing more than average weight';
title2 'PROC SQL - Just 1 Step';
proc sql ;
 select mean(weight) into: meanweight_x 
  FROM sashelp.class ;
 select name, sex, weight format=6.1
  FROM sashelp.class 
 having weight > &meanweight_x;
quit;

Students weighing more than average weight

PROC SQL - Just 1 Step

100.0263

---

Students weighing more than average weight

PROC SQL - Just 1 Step

Name	Sex	Weight
Alfred	M	112.5
Carol	F	102.5
Henry	M	102.5
Janet	F	112.5
Mary	F	112.0
Philip	M	150.0
Robert	M	128.0
Ronald	M	133.0
William	M	112.0