topic Re: Create new variables by lookup across row in SAS Programming

Create new variables by lookup across row

ari — Thu, 29 Sep 2016 20:15:48 GMT

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.

Re: Create new variables by lookup across row

Reeza — Thu, 29 Sep 2016 17:57:53 GMT

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;

Re: Create new variables by lookup across row

Astounding — Thu, 29 Sep 2016 18:05:37 GMT

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?)

Re: Create new variables by lookup across row

ari — Thu, 29 Sep 2016 20:05:56 GMT

@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.

Re: Create new variables by lookup across row

ari — Fri, 30 Sep 2016 06:16:52 GMT

@Reeza, thanks for the help.

Re: Create new variables by lookup across row

Reeza — Thu, 29 Sep 2016 20:14:15 GMT

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

Re: Create new variables by lookup across row

Astounding — Thu, 29 Sep 2016 21:25:06 GMT

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;

Re: Create new variables by lookup across row

Ksharp — Fri, 30 Sep 2016 04:09:03 GMT

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;