Creating dynamic macro lists of variable values

[BillJones]

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;

[Haikuo · Accepted solution]

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

[Jagadishkatam]

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

[BillJones]

Jagadishkatam,

Thanks for the alternative code.  Much appreciated.

Regards,

Bill

[BillJones]

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