Creating macro variables lists based on the value of another variable

[sgnolek]

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;

[pradeepalankar]

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

[Reeza · Accepted solution]

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

[sgnolek]

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