Creating macro lists of variable names based on different criteria

Question

I have a very wide dataset of population denominators by age and race that (simplified for this question) looks like this:

the variable name carries the information of who the population number describes by race & age for each zip code. so w_1 are white 1 year olds, b_1 are black one year olds, a for asian and this goes from ages 1-100.

my task is to sum the population at each zip code by various different cuts. for example ages 5+, 5-11, 12-17, 65+, by each race group, by race*age group. Naturally, I don't want to type out w_1 + w_2 + ...+ w_100 for all the different categories.

My strategy is to pull the relevant variable names into a macro list for each category that I want a population sum. I've already pulled the variable names into a dataset called "varname" and used substr() to assign the variable name age & rage data so it can be easily filtered. Now I want to filter that varname dataset for each race type and create a macro list of all the variables. here's what my code looks like:

    %let racecat = white black aian asian nhppi latinx;
    %let race = 'W' 'B' 'I' 'A' 'P' 'H';

    %macro varlist_race;
    %let varnum= %sysfunc(countw(&racecat.));
    %do i=1 %to &varnum.;
        %let nextgroup= %scan(&racecat.,&i);

        proc sql;
            select strip(name)
            into :&nextgroup separated by ","
            from varname
            where race =%scan(&race.,&i) ;
        quit;
    %end; %mend varlist_race;
    %varlist_race;

what I expected was a macro list for each racecat like &white &black etc. When I run this code my results viewer is printing out the multiple variable name lists correctly but the macro lists don't seem to be saving. for example I'm getting this error afterward.

%put &white;

WARNING: Apparent symbolic reference WHITE not resolved.

the end usage will look like this:

    data pop2019_sums;
    set pop2019;
     pop_white = sum(white);
     pop_black = sum(&black);
     pop_asian= sum(&asian);
    run;

I have no clue why these macro lists aren't saving after the do loop. any feedback is greatly appreciated!

Restructure your data and make your life a lot easier. This is a very complicated data structure whereas a more structured file would make this very trivial to do. — Reeza
Create a data set that has the headers instead, zip_code, race, age, count. So a long format data set in general. — Reeza
@Reeza this literally never crossed my mind and yeah, it's infinitely faster and easier. thank you so much — Megan Halbrook

Reeza Reeza · Accepted Answer · 2021-11-09T22:58:21

Macro variables have a concept known as scope. Local macro variables only exist within the macro and do not exist once the macro ends.

You have to explicitly change it to global so it exists outside the macro by using the %GLOBAL statement.

%let racecat = white black aian asian nhppi latinx;
%let race = 'W' 'B' 'I' 'A' 'P' 'H';

%macro varlist_race;
%let varnum= %sysfunc(countw(&racecat.));
%do i=1 %to &varnum.;
    %global %scan(&racecat.,&i);
    %let nextgroup= %scan(&racecat.,&i);


    proc sql;
        select strip(name)
        into :&nextgroup separated by ","
        from varname
        where race =%scan(&race.,&i) ;
    quit;
%end; 
%mend varlist_race;
%varlist_race;

That being said, this is very cumbersome and inefficient. If you transpose your data so that you have it structured as:

  ZipCode  Age  Race  Count

Then you can quite easily summarize for various age groups easily, using proc summary/means including any overlapping ranges as well using a multilabel format, examples here.

Creating macro lists of variable names based on different criteria

1 Answers