Lesson 15, Part 2: Creating a horizontal list of values from a variable (SAS vs. Python)¶

  • Create Macro variables using the 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
In [4]:
import saspy
sas = saspy.SASsession(cfgname='winlocal')    # or your SASPy configuration

SAS Connection established. Subprocess id is 89704
In [13]:
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'])

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.01 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

E3969440A681A2408885998500000007

Line 3 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 5 initiates an SQL procedure in SAS. The noprint option suppresses the automatic printing of results to the output window.

Line 6 selects unique (distinct) values of the 'name' column, with any leading or trailing spaces removed (trim).

Line 7 creates a macro variable named AFR_clist. The selected values will be stored in this variable, separated by commas.

Line 8 specifies the dataset to query. In this case, it's the 'demographics' dataset from the SASHELP library.

Line 9 is the WHERE clause that filters the data. It only selects rows where the 'region' column equals "AFR" (likely standing for Africa).

Line 10 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.

In [6]:
import pandas as pd

# Read the SASHELP.DEMOGRAPHICS data set into a pandas DataFrame
df = sas.sasdata2dataframe(table='demographics', libref='sashelp')

# Display basic information
print("Variables:")
print(df.columns.tolist())

print("\nFirst five observations:")
print(df.head())

# Select unique country names for the Africa (AFR) region
afr_clist = (
    df.loc[df['region'] == 'AFR', 'NAME']
      .dropna()
      .unique()
      .tolist()
)

# Optional: Sort alphabetically
afr_clist.sort()

# Convert list to a comma-separated string
afr_clist_str = ", ".join(afr_clist)

# Output results
print(f"\nNumber of countries in AFR = {len(afr_clist)}")
print(f"\nAFR country list:\n{afr_clist_str}")

Variables:
['CONT', 'ID', 'ISO', 'NAME', 'ISONAME', 'region', 'pop', 'popAGR', 'popUrban', 'totalFR', 'AdolescentFPpct', 'AdolescentFPyear', 'AdultLiteracypct', 'MaleSchoolpct', 'FemaleSchoolpct', 'GNI', 'PopPovertypct', 'PopPovertyYear']

First five observations:
   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 in AFR = 46

AFR country list:
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