DATA Step, Macro, Functions and more

Create new variables by lookup across row

Reply
Frequent Contributor
Frequent Contributor
Posts: 103

Create new variables by lookup across row

[ Edited ]

Hi,

 

I am trying new variables by looking across the different variables (columns) in a row (observation).

have:

idabc
1221
2310
3100
4123
5321

 

want:

 

idabcg1g2g3
1221110
2310101
3100100
4123111
5321111

 

If the first row (from a to c) contains value 1 then  g1 will be set to 1 else 0.  if  the 1st row (from a to c) contains value 2 then g2 set to 1 else 0 and so on. 

 

Any help to prepare this dataset in sas?

 

Thanks.

Super User
Posts: 19,855

Re: Create new variables by lookup across row

Declare two arrays, one for old and one for new variables. 

 

Loop through (do loop) and use whichN to search for each value. 

 

Array old(*) old1-old10;

array new(*) new1-new10 (10*0);

 

do i=1 to dim(new);

new(I) = whichn(i, of old(*)) > 0;

end;

Frequent Contributor
Frequent Contributor
Posts: 103

Re: Create new variables by lookup across row

[ Edited ]

@Reeza, thanks for the help.

Super User
Posts: 19,855

Re: Create new variables by lookup across row

So change the array lengths and the do loop to match your criteria. If you run into issues, post your code.

Super User
Posts: 5,516

Re: Create new variables by lookup across row

The suggestion you received about arrays and WHICHN is a good approach.  However, to get it exactly right, you may need to supply a few more details.  ("And so on" doesn't count.)

 

Do you need a variable for g0?

 

Is g3 the maximum that  you would need?  (If not, what is the maximum?)

Frequent Contributor
Frequent Contributor
Posts: 103

Re: Create new variables by lookup across row

[ Edited ]
Posted in reply to Astounding

@Astounding There are about 100 input  variables. g3 is the maximum. So, I would need from g0 (minimum) to g3 (maximum).

Thanks for the help.

Super User
Posts: 5,516

Re: Create new variables by lookup across row

It's probably easiest to change the dimensions of the G array to include 0:

 

array old {100} list all the names here ....;

array g {0:3} g0 - g3;

 

do _n_=0 to 3;

   g{_n_} = whichn(_n_, of old{*}) > 0;

end;

Super User
Posts: 10,044

Re: Create new variables by lookup across row

data have;
infile cards dlm='09'x truncover;
input id	a	b	c;
cards;
1	2	2	1
2	3	1	0
3	1	0	0
4	1	2	3
5	3	2	1
;
run;


data _null_;
 set have end=last;
 retain max;
 temp=max(of a--c);
 if temp gt max then max=temp;
 if last then call symputx('n',max);
 drop temp;
run;
data want;
 set have;
 array x{*} a--c;
 array g{*} g1-g&n;
 do i=1 to dim(g); 
  if i in x then g{i}=1;
   else g{i}=0;
 end;
 drop i;
run;

Ask a Question
Discussion stats
  • 7 replies
  • 424 views
  • 2 likes
  • 4 in conversation