Condense a data set variables columns into one

[rbikes]

Hello,

I am trying to create a condensed dataset from one that has a stepwise apperence. I started by thinking of listing the column varables names and setting those varables names that repeat to the same column. I did this by collecting the names into double ampersand macro varables list, one appersand for the subgroup name and the other appersand for the count of repeated varables. Then I conducted a %if %else condition with %index. Now it seemed to work fine, but the  and find some TRUE results, but it dones't seem to like to output to static varable names:

%else %if %index(&&b&i, 2)>0 %then C3=resolve('&&b&i');

MLOGIC(DOIT):  %IF condition %index(&&b&i, 2)>0 is TRUE
NOTE: Line generated by the invoked macro "DOIT".
36     C3=resolve('&&b&i')
       --
       22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >,
              ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

Here bellow is a relpicated issue:

%let a1 = a5;
%let a2 = a6;
%let a3 = a7;
%let a4 = a8;
%let obs = 4;

data prop;
	input a5 a6 a7 a8;
	n=_n_;
cards;
1 . . .
. 2 . .
. 2 . .
. . 3 .
. . 3 .
. . 3 .
. . . 4
. . . 4
. . . 4
. . . 4
;
run;
options mlogic;
%macro doit1();
data prop2;
	length f 3.;
	set prop;
	by n;
	%do i=1 %to &obs;
		%if %index(&&a&i,5)>0 %then e=&&a&i;
		%else %if %index(&&a&i,6)>0 %then e=&&a&i;
		%else %if %index(&&a&i,7)>0 %then e=&&a&i;
		%else %if %index(&&a&i,8)>0 %then e=&&a&i;
	%end;
run;
%mend;

%doit1;
options nomlogic;

[ballardw · Accepted solution]

You are mixing Macro and base SAS code.

You don't show what you actually want this to accomplish but take a look at the results from this:

data want;
   set prop;
   a5 = coalesce(a5,a6,a7,a8);
run;

It moves all of the values from A5 to A8 into A5. You could drop the A6-A8 variables if you don't need them after this step.

[rbikes]

Thanks for replying ballardw!

The function is great! I appericate your time, next time I will spicify what I am looking for.

rbikes