Create new variables by lookup across row

Frequent Contributor
Posts: 106

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:

 id a b c 1 2 2 1 2 3 1 0 3 1 0 0 4 1 2 3 5 3 2 1

want:

 id a b c g1 g2 g3 1 2 2 1 1 1 0 2 3 1 0 1 0 1 3 1 0 0 1 0 0 4 1 2 3 1 1 1 5 3 2 1 1 1 1

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: 23,663

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
Posts: 106

Re: Create new variables by lookup across row

[ Edited ]

@Reeza, thanks for the help.

Super User
Posts: 23,663

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: 6,751

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
Posts: 106

Re: Create new variables by lookup across row

[ Edited ]

@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: 6,751

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,761

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;

```
Discussion stats
• 7 replies
• 440 views
• 2 likes
• 4 in conversation