topic Re: Creating dynamic macro lists of variable values in SAS Programming

Creating dynamic macro lists of variable values

BillJones — Sat, 19 Oct 2013 08:57:24 GMT

Hello,

I'm trying to create several macro lists of variable values. I know the maximum number of elements that I want in each list. However, I don't know how many lists that I'll need to create. Furthermore, the data set with the variable values that I want to convert into macro lists is fairly large, somewhere around 300k records. Therefore, I don't believe that I can put all the values in one macro list and then use the word count macro to parse them into smaller lists. I have some example code below which I use proq sql to convert the variable values into macro variables and use the &NObs value to determine how many lists should be generated as well as the start value and end value of each list. I believe that's all working fine. The issue I'm having is with the syntax to create the actual lists (in the idlist macro, lines 63-65). I'm trying to use a do loop with &&, but can't get it to work. Also, I want to wrap each macro list in single quotes and use a + sign between each element except for the last one. Does anyone know of a straight forward way to create such a series of lists? Any help or advice would be greatly appreciated!

Regards,

Bill

dm 'clear log';

*In this example code, I want to create 4 macro lists:

idlist1='abc12345678+bbc12345678+cbc12345678'

idlist2='dbc12345678+ebc12345678+fbc12345678'

idlist3='gbc12345678+hbc12345678+ibc12345678'

idlist4='jbc12345678+kbc12345678'

;

data test;

format id $11.;

input id;

cards;

abc12345678

bbc12345678

cbc12345678

dbc12345678

ebc12345678

fbc12345678

gbc12345678

hbc12345678

ibc12345678

jbc12345678

kbc12345678

;

run;

proc sql noprint;

select count(*)

into :NObs

from test;

select id

into :id1-:id%left(&NObs)

from test;

quit;

%put &id1 &id9;

%let num_lists=%sysevalf(%trim(&NObs.)/3,ceil);

%put &num_lists;

%macro idlist();

%do i=1 %to &num_lists;

%let start=%eval((&i.*3)-2);

%put &start;

%if &i < &num_lists %then %do; %let end=%eval(&i.*3); %end;

%else %do; %let end=&NObs; %end;

%put &end;

%do z=&start %to &end;

%let idlist&i=&&id&z.||'+';

%end;

%put &idlist&i;

%end;

%mend;

%idlist;

Re: Creating dynamic macro lists of variable values

Haikuo — Sat, 19 Oct 2013 13:53:29 GMT

Here is an data step approach without involving complex Macros,

data test;

format id $11.;

input id;

cards;

abc12345678

bbc12345678

cbc12345678

dbc12345678

ebc12345678

fbc12345678

gbc12345678

hbc12345678

ibc12345678

jbc12345678

kbc12345678

;

run;

data _null_;

length _cat _mac $ 100;

do n=1 to 3 while (not last) ;

set test end=last;

_cat=catx('+',_cat,id);

end;

_mac=cats('idlist',_n_);

call symputx(_mac,_cat);

if last then stop;

run;

%put _user_;

Re: Creating dynamic macro lists of variable values

Jagadishkatam — Sat, 19 Oct 2013 14:52:05 GMT

The code of Hai Kuo is more efficient, this below code is just an alternative

/*to get the values into different groups*/

data want;

j=0;

do until(eof);

j+1;

do i=1 to 3;

set test end=eof;

output;

end;

run;

/*transpose the every three values into rows specific to group*/

proc transpose data=want out=trans;

by j;

var id;

run;

/*to generate the macto varibles*/

proc sql;

select catx('+',col1,col2,col3) into :idlist1-:idlist&sysmaxlong from trans;

quit;

%put &idlist1;

%put &idlist2;

%put &idlist3 ;

%put &idlist4;

Thanks,

Jagadish

Re: Creating dynamic macro lists of variable values

BillJones — Sat, 19 Oct 2013 21:25:06 GMT

Haikuo,

Thanks so much for the code. Your solution is very straightforward and executes quickly. The idea that I had with the macros was too cumbersome. Using the data step approach is much more elegant. Thanks again!

Regards,

Bill

Re: Creating dynamic macro lists of variable values

BillJones — Sat, 19 Oct 2013 21:27:29 GMT

Jagadishkatam,

Thanks for the alternative code. Much appreciated.

Regards,

Bill