Lesson 9, Part 1b: Macro variables for table Look-up¶
ods html close;
Options nocenter nodate nonumber;
Title1 "Look-up using a macro variable";
Title2 "that contains a numeric value as text";
%let size=200000000;
proc print data=sashelp.demographics noobs;
var name pop;
where pop>&size;
run;
| NAME | pop |
|---|---|
| UNITED STATES | 298,212,895 |
| CHINA | 1,323,344,591 |
| INDIA | 1,103,370,802 |
| INDONESIA | 222,781,487 |
options Options nocenter nodate nonumber symbolgen;
Title1 "Look-up using a macro variable";
Title2 "that contains a character value";
%let c_name=QATAR;
proc print data=sashelp.demographics noobs;
var name pop;
where name = "&c_name";
run;
| NAME | pop |
|---|---|
| QATAR | 812,842 |
options nocenter nodate nonumber;
Title1 "Look-up using a macro variable";
Title2 "that contains a quoted character value";
%let c_name='QATAR';
proc print data=sashelp.demographics noobs;
var name pop;
where name = &c_name;
run;
| NAME | pop |
|---|---|
| QATAR | 812,842 |
List observations having the country names that begin with Q or Z in the SASHELP.DEMOGRAPHICS data set. Using IN:( ) to Code Character Comparisons with Criteria Having Different Lengths - Global Forum paper by Paul Grant (2009)
*Ex5_Lookup_mvar.sas (Part 5);
options nocenter nodate nonumber symbolgen;
Title1 "Look-up using a macro variable";
Title2 "that contains character values";
%let name_QZ=('Q', 'Z');
proc print data=sashelp.demographics noobs;
var name pop;
where name in: &name_QZ;
run;
title;
| NAME | pop |
|---|---|
| ZAIRE | 57,548,744 |
| ZIMBABWE | 13,009,534 |
| ZAMBIA | 11,668,457 |
| QATAR | 812,842 |
Creating Data-Dependent Values of a Macro Variable for Table Look-Up¶
proc sql ;
select quote(strip(Name))
INTO :Starts_withC separated by ','
from sashelp.demographics
where Name LIKE "C%";
quit;
%PUT &Starts_withC;
| "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" |
*Ex9_Macro_In_Operator.sas;
proc print data=sashelp.demographics;
var Name pop;
where Name in (&Starts_withC);
run;
| Obs | NAME | pop |
|---|---|---|
| 3 | CANADA | 32,268,243 |
| 4 | COSTA RICA | 4,327,228 |
| 5 | CUBA | 11,269,400 |
| 22 | CHILE | 16,295,102 |
| 23 | COLOMBIA | 45,600,244 |
| 45 | CZECH REPUBLIC | 10,219,603 |
| 54 | CROATIA | 4,551,338 |
| 86 | CAMEROON | 16,321,863 |
| 87 | CAPE VERDE | 506,807 |
| 88 | CENTRAL AFRICAN REP. | 4,037,747 |
| 89 | CHAD | 9,748,931 |
| 90 | COMOROS | 797,902 |
| 91 | CONGO | 3,998,904 |
| 141 | CAMBODIA | 14,071,014 |
| 143 | CHINA | 1,323,344,591 |
| 144 | CYPRUS | 835,307 |
| 186 | COOK ISLANDS | 17,954 |
| 187 | CORAL SEA ISLANDS | 20,155,129 |
ods html close;
options Options nocenter nodate nonumber nosource nonotes nosymbolgen;
data _null_;
today_date = put(date(), worddate18.);
put today_date=;
run;
The SAS System
NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1
today_date=April 8, 2026
The SAS System
E3969440A681A2408885998500000032
The TITLE statement requires double quotes. Single quotes prevent all macro functions.
Macro functions (%left and %qsysfunc) are resolved inside the quotes.
%qsysfunc is used to handle special characters like commas safely.
title "SASHELP.CLASS - %left(%qsysfunc(date(),worddate18.))" ;
proc print data=sashelp.class (obs=5);
run;
| Obs | Name | Sex | Age | Height | Weight |
|---|---|---|---|---|---|
| 1 | Alfred | M | 14 | 69.0 | 112.5 |
| 2 | Alice | F | 13 | 56.5 | 84.0 |
| 3 | Barbara | F | 13 | 65.3 | 98.0 |
| 4 | Carol | F | 14 | 62.8 | 102.5 |
| 5 | Henry | M | 14 | 63.5 | 102.5 |
When to quote a macro variable reference in SAS¶
Note that a macro variable value is neither character nor numeric. It is just text.
SAS decides what type it is based on the context in which it is used. In the example below, x is a numeric variable, and y is a character variable.
ods html close;
options nodate nonumber nosource;
%let year=2018;
data test;
x=&year; /* numeric assignment */
y="&year"; /* character assignment */
run;
The SAS System NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 NOTE: The data set WORK.TEST has 1 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds The SAS System E3969440A681A2408885998500000008
What happens in the code above:¶
- x = &year;
- Resolves to x = 2018;
- x is numeric because no quotes → numeric literal.
- y = "&year";
- Resolves to y = "2018";
- y is a character because quotes are used.
proc sql;
select varnum, name, type
from dictionary.columns
where libname = 'WORK' and memname = 'TEST';
quit;
| Column Number in Table | Column Name | Column Type |
|---|---|---|
| 1 | x | num |
| 2 | y | char |
Code Explanation¶
DICTIONARY.COLUMNS (part of the DICTIONARY tables family) is a read-only table in SAS that contains information about all variables for all datasets currently accessible in your session. In the code snippet below, we use a SQL query to extract the metadata of the TEST dataset.