Lesson 14, Part 7: Creating a horizontal list of values from a variable¶
- Create Macro variables using PROC SQL Into clause in SAS
- Convert unique values to a Python List in Python
- Create multiple lists from various variables
- SAS Macro (multiple ways) vs. Python Function
Data The Sashelp.demographics data set provides the 2004 revision of data derived from world population prospects. The data set contains 197 observations and 18 variables, including the following three (i.e., NAME, pop, and region). Below are the attributes of those three variables.
NAME TYPE Label
NAME Character GLC Country Name
pop Numeric Population (2005)
region Character Region
import saspy
sas = saspy.SASsession(cfgname='winlocal') # or your SASPy configuration
SAS Connection established. Subprocess id is 116556
sas_code = """
/*Task 1: Create a list of country names for region="AFR" using SAS */
dm "log; clear; output; clear; odsresults; clear;";
options nocenter nodate nonumber nosource;
proc sql noprint;
select distinct trim(name)
INTO :AFR_clist separated by ','
FROM SASHELP.demographics
where region="AFR";
quit;
%put NOTE: Processing region AFR (Nonmacro code);
%put Number of countries for AFR_clist = &sqlobs; %put &=AFR_clist;
%put NOTE: End of region AFR processing;
"""
results = sas.submit(sas_code)
print(results['LOG'])
print(results['LST'])
5 The SAS System 02:22 Thursday, July 2, 2026
24 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
24 ! ods graphics on / outputfmt=png;
NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1
25
26
27 /*Task 1: Create a list of country names for region="AFR" using SAS */
28 dm "log; clear; output; clear; odsresults; clear;";
29 options nocenter nodate nonumber nosource;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
NOTE: Processing region AFR (Nonmacro code)
Number of countries for AFR_clist = 46
AFR_CLIST=ALGERIA,ANGOLA,BENIN,BOTSWANA,BURKINA FASO,BURUNDI,CAMEROON,CAPE VERDE,CENTRAL AFRICAN REP.,CHAD,COMOROS,CONGO,EQUATORIAL
GUINEA,ERITREA,ETHIOPIA,GABON,GAMBIA,GHANA,GUINEA,GUINEA-BISSAU,IVORY
COAST,KENYA,LESOTHO,LIBERIA,MADAGASCAR,MALAWI,MALI,MAURITANIA,MAURITIUS,MOZAMBIQUE,NAMIBIA,NIGER,NIGERIA,RWANDA,SAO
TOME/PRINCIPE,SENEGAL,SEYCHELLES,SIERRA LEONE,SOUTH AFRICA,SWAZILAND,TANZANIA,TOGO,UGANDA,ZAIRE,ZAMBIA,ZIMBABWE
NOTE: End of region AFR processing
The SAS System
E3969440A681A2408885998500000003
Line 1 uses the DM statement to clear the log, output, and ODS results windows. This one-liner is often used to clean up the SAS environment before running new code.
Line 2 initiates an SQL procedure in SAS. The noprint option suppresses the automatic printing of results to the output window.
Line 3 selects unique (distinct) values of the 'name' column, with any leading or trailing spaces removed (trim).
Line 4 creates a macro variable named AFR_clist. The selected values will be stored in this variable, separated by commas.
Line 5 specifies the dataset to query. In this case, it's the 'demographics' dataset from the SASHELP library.
Line 6 is the WHERE clause that filters the data. It only selects rows where the 'region' column equals "AFR" (likely standing for Africa).
Line 7 ends the SQL procedure.
In summary, this code creates a macro variable (:AFR_clist) containing a comma-separated list of all unique country names in the Africa region from the SASHELP.demographics dataset. This list could be used later in your SAS program for further processing or analysis of African countries.
import pandas as pd
# Load dataset from SAS
df = sas.sasdata2dataframe(table='demographics', libref='sashelp')
print(df.columns)
print(df.head())
# Select distinct countries for AFR region
afr_clist = df[df['region'] == 'AFR']['NAME'].unique().tolist()
# Convert list to comma-separated string
afr_clist_str = ','.join(afr_clist)
# Output results
print(f"Number of countries for AFR_clist = {len(afr_clist)}")
print(f"AFR_clist = {afr_clist_str}")
Index(['CONT', 'ID', 'ISO', 'NAME', 'ISONAME', 'region', 'pop', 'popAGR',
'popUrban', 'totalFR', 'AdolescentFPpct', 'AdolescentFPyear',
'AdultLiteracypct', 'MaleSchoolpct', 'FemaleSchoolpct', 'GNI',
'PopPovertypct', 'PopPovertyYear'],
dtype='object')
CONT ID ISO NAME ISONAME region pop popAGR \
0 91.0 180.0 44.0 BAHAMAS BAHAMAS AMR 323063.0 0.013370
1 91.0 227.0 84.0 BELIZE BELIZE AMR 269736.0 0.021354
2 91.0 260.0 124.0 CANADA CANADA AMR 32268243.0 0.008714
3 91.0 295.0 188.0 COSTA RICA COSTA RICA AMR 4327228.0 0.020412
4 91.0 300.0 192.0 CUBA CUBA AMR 11269400.0 0.003427
popUrban totalFR AdolescentFPpct AdolescentFPyear AdultLiteracypct \
0 0.900 2.3 NaN NaN NaN
1 0.486 3.1 0.125 1998.0 0.769
2 0.811 1.5 0.065 1997.0 NaN
3 0.617 2.2 0.174 1999.0 0.958
4 0.760 1.6 0.160 2000.0 0.998
MaleSchoolpct FemaleSchoolpct GNI PopPovertypct PopPovertyYear
0 0.85 0.88 16140.0 NaN NaN
1 0.98 1.00 6510.0 NaN NaN
2 1.00 1.00 30660.0 NaN NaN
3 0.90 0.91 9530.0 0.02 2000.0
4 0.96 0.95 NaN NaN NaN
Number of countries for AFR_clist = 46
AFR_clist = ALGERIA,ANGOLA,BOTSWANA,BURUNDI,CAMEROON,CAPE VERDE,CENTRAL AFRICAN REP.,CHAD,COMOROS,CONGO,ZAIRE,BENIN,ERITREA,EQUATORIAL GUINEA,ETHIOPIA,GABON,GAMBIA,GHANA,GUINEA,IVORY COAST,KENYA,LESOTHO,LIBERIA,MADAGASCAR,MALAWI,MALI,MAURITANIA,MAURITIUS,MOZAMBIQUE,NIGER,NIGERIA,GUINEA-BISSAU,RWANDA,SAO TOME/PRINCIPE,SENEGAL,SEYCHELLES,SIERRA LEONE,SOUTH AFRICA,ZIMBABWE,NAMIBIA,SWAZILAND,TANZANIA,TOGO,UGANDA,BURKINA FASO,ZAMBIA