SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Condense a data set variables columns into one

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Condense a data set variables columns into one

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;

Accepted Solutions
Solution
‎05-02-2016 04:51 PM
Super User
Posts: 11,343

Re: Condense a data set variables columns into one

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.

 

View solution in original post


All Replies
Solution
‎05-02-2016 04:51 PM
Super User
Posts: 11,343

Re: Condense a data set variables columns into one

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.

 

Occasional Contributor
Posts: 12

Re: Condense a data set variables columns into one

Thanks for replying ballardw!

 

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

 

rbikes

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 282 views
  • 1 like
  • 2 in conversation