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;
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_;
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_;
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;
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
Jagadishkatam,
Thanks for the alternative code. Much appreciated.
Regards,
Bill
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
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.