I'm trying to concatenate columns when the number of columns exceeds a count of 5, but no effect on those at 5 and under. I don't know what the max column count will be (almost guaranteed to be under 20) as data is regular refreshed, but I have a counter to check each row. My data currently looks like:

I am aiming for

My attempt at a solution is this:

*for cases of count 6 to 10*
%let col = c;
%macro catx;
data want;
set have;
%do i = 1 %to %sysfunc(ceiling((count/2))) %by 1;
%do j = 1 %to count %by 2;
col&i = %sysfunc(catx(',' &c&j, &c&&j 1));
%end;
%end;
run;
%mend;

Then for cases where count > 10, i and j become %do i = 1 %to %sysfunc(ceiling((count/3))) %by 1; and %do j = 1 %to count %by 3; For cases of count > 15 values change to 4, 5 for count > 20 and so on.

I'm not able to get the loops to work properly and not sure how to implement the conditions for when 5<count<10<count<15<count<20.

TIA.

CodePudding user response：

Macro code is for generating SAS code. For this problem there is no need to generate any SAS code.

So assuming you want to make NEW variables that contain the concatenated pairs you could do something like this with arrays.

 data want;
   set have;
   array in c1-c10 dummy;
   array out $50 new1-new5 ;
   target=0;
   do index=1 to count by 2;
     target=target 1;
     out[target]=catx(',',in[index],in[index 1]);
   end;
   drop dummy index target;
 run;

If the number of variables in the dataset can vary then it might help to use a macro variable to set the upperbound on the variable list.

%let nvar=10;
%let nvar2 = %sysfunc(ceil(&nvar/2));
...
array in c1-c&nvar. dummy;
array out $50 new1-new&nvar2. ;