topic Assign Rows of Table to Macro Variables in SAS Programming

Assign Rows of Table to Macro Variables

tburus — Wed, 02 Sep 2020 05:00:56 GMT

I have an n x 1 table where I want to assign the values of the rows to macro variables for use in model building. There will be several such tables, all of varying lengths, created during the course of an automated program (so I don't know the number of rows at any given point). I am struggling to think of a way to generalize the code to accomplish this task.

Test data:

data test;
	input name $;
	datalines;
act_a
act_b
act_c
act_d
	;
run;

Re: Assign Rows of Table to Macro Variables

PeterClemmensen — Wed, 02 Sep 2020 05:09:41 GMT

One way

data test;
input name $;
datalines;
act_a
act_b
act_c
act_d
;

 proc sql noprint;
   select name into :n1 -
   from test;
   %let num = &sqlobs;
 quit;

 %put &=n1 &=n2 &=n3 &=n4 &=num;

Re: Assign Rows of Table to Macro Variables

andreas_lds — Wed, 02 Sep 2020 05:09:57 GMT

Rule of thumb: moving data into macro-variables is a bad idea in almost all cases i can think of, because when creating a lot of variables, or one variable containing a list of values, you will have to write loops to process the information. The BY-statement (usable in many procedures) exists to avoid such coding.

But since model building is something not on my skill list, creating those macro-variables could be the easiest way to solve your problem - i doubt it.

/* On variable, values separated by | */
proc sql noprint;
  select name into :nameList separated by '|'
    from test;
quit;

/* One variable for each value */
data _null_;
  set test;
  call symputx(cats('test_', _n_), name);
run;

Re: Assign Rows of Table to Macro Variables

tburus — Wed, 02 Sep 2020 05:19:06 GMT

Thank you for your caution. If it helps, here is what the model building looks like:

proc surveyfreq data=GLSDesign_basb3;
   	strata strata;
	weight nationalweight;
	tables sport_name_girls_softball*(act_a--act_d)/ or noprint;
run;

The difficulty lies in that sometimes the list may go to act_e or beyond, or there may not be an act_a at the beginning, etc. In order to automate this I need something that generalizes past those uncertainties.

Re: Assign Rows of Table to Macro Variables

PeterClemmensen — Wed, 02 Sep 2020 06:02:11 GMT

@tburus if you want to use all variables with the naming pattern act_*** in your tables statement, simply use a variable list like below. I use an example data set, since I don't have access to your data.

proc surveyfreq data=sashelp.baseball;
   	strata league;
	weight salary;
	tables team*(n:) / or noprint;
run;

If you insist on the macro variables approach, see below

data test;
input name $;
datalines;
act_a
act_b
act_c
act_d
;

proc sql noprint;
    select name into :n1 -
    from test;
    %let num = &sqlobs;
quit;

%put &n1 &&n&num;

options symbolgen;
proc surveyfreq data=GLSDesign_basb3;
   	strata strata;
	weight nationalweight;
	tables sport_name_girls_softball*(&n1--&&n&num)/ or noprint;
run;

Re: Assign Rows of Table to Macro Variables

tburus — Wed, 02 Sep 2020 06:25:38 GMT

Thanks. I'm not quite sure I see how to implement the first method you mentioned. I have included a list of variable names from one of the tables being used to build the models (these come from a set of dummy variables created using GLMSelect):

Re: Assign Rows of Table to Macro Variables

ChrisNZ — Wed, 02 Sep 2020 08:34:28 GMT

Try this:

proc surveyfreq data=GLSDESIGN_BASB3;
  strata STRATA;
  weight NATIONALWEIGHT;
  tables SPORT_NAME_GIRLS_SOFTBALL*(ACT_:)/ or noprint;
run;

Re: Assign Rows of Table to Macro Variables

PaigeMiller — Wed, 02 Sep 2020 10:50:11 GMT

@tburus wrote:

Thanks. I'm not quite sure I see how to implement the first method you mentioned. I have included a list of variable names from one of the tables being used to build the models (these come from a set of dummy variables created using GLMSelect):

I would use the first method from @andreas_lds where the separator is a space, rather than a vertical bar as he had. Then your code

proc surveyfreq data=GLSDesign_basb3;
   	strata strata;
	weight nationalweight;
	tables sport_name_girls_softball*(&namelist)/ or noprint;
run;

is perfectly general, works for any text in data set TEST, and any number of rows in the data set TEST (assuming the text is a legal SAS variable name).

Re: Assign Rows of Table to Macro Variables

tburus — Wed, 02 Sep 2020 15:58:35 GMT

I can honesty say I am blown away that works. Thanks a bunch.