topic Re: Creating macro variables lists based on the value of another variable in SAS Programming

Creating macro variables lists based on the value of another variable

sgnolek — Mon, 24 Mar 2014 19:33:40 GMT

I want to be able to dynamically define a series of macro variables based on the value of another variable. The easiest example to describe this would be... say I have a list of reporting variables (A_rpt, B_rpt, C_rpt...) and I know what pages I want them to appear on when I create some ODS output. So imagine I have a data set like this:

RPT_Variable Page

A_rpt 1

B_rpt 1

C_rpt 1

D_rpt 2

E_rpt 3

F_rpt 3

I want to create three different macro variable lists that would contain the following

List1 = A_rpt B_rpt C_rpt

List2 = D_rpt

List3 = E_rpt F_rpt

I can find lots of examples of how to put a single list into a macro variable (either using %let, or SQL), or how to create a series of macro variables using SQL (shown below), but any hints how to do something that is somewhere in between, either by modifying the code below or taken a different approach?

Thanks for any help you can provide

%macro rptlist;

proc sql noprint;

select distinct rpt_variable into:rptvar1-:rptvar999 from set_my_page;

select page into:page1-:page999 from set_my_page;

quit;

%do i = 1 %to&sqlobs;

%put &&rpt_var&i &&page&i;

%end;

%mend;

%varlist;

Re: Creating macro variables lists based on the value of another variable

pradeepalankar — Mon, 24 Mar 2014 19:56:01 GMT

hi try this:

data test;

input RPT_Variable $ page;

cards;

A_rpt 1

B_rpt 1

C_rpt 1

D_rpt 2

E_rpt 3

F_rpt 3

;

proc sql noprint;

select distinct page into :pages separated by '|' from test;

quit;

%macro test;

%PUT &PAGES;

%do i= 1 %to %sysfunc(countw(&pages,'|'));

%let list=%scan(&pages,&i,'|');

proc sql noprint;

select RPT_Variable into :list_&list separated by ' ' from test

where page=&list;

quit;

%put list_&list-->&&list_&list;

%end;

%mend;

%test;

output:

list_1-->A_rpt B_rpt C_rpt

list_2-->D_rpt

list_3-->E_rpt F_rpt

Re: Creating macro variables lists based on the value of another variable

Reeza — Mon, 24 Mar 2014 20:07:14 GMT

A standard data step works nicely as well.

DATA SAMPLE;

LENGTH VAR_LIST $256.;

SET HAVE;

BY PAGE;

RETAIN VAR_LIST;

IF FIRST.PAGE THEN VAR_LIST=RPT_VARIABLE;

ELSE VAR_LIST=CATX(" ", VAR_LIST, RPT_VARIABLE);

TEST=FIRST.PAGE;

IF LAST.PAGE THEN DO;

CALL SYMPUTX("PAGE"||PUT(PAGE, 2. -L), VAR_LIST);

END;

RUN;

%PUT &PAGE1.;

%PUT &PAGE2.;

%PUT &PAGE3.;

%PUT &PAGE4.;

Re: Creating macro variables lists based on the value of another variable

sgnolek — Mon, 24 Mar 2014 20:45:13 GMT

Thanks to both of you for very helpful answers that worked great. Much appreciated!!