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;
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.;
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
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.;
Thanks to both of you for very helpful answers that worked great. Much appreciated!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.