topic Re: create 2 new vars a new and old from 2 rows in SAS Procedures

create 2 new vars a new and old from 2 rows

podarum — Mon, 07 Jan 2013 20:18:51 GMT

Hi, I'm trying to create 2 new vars from 2 rows (eg. and old and a new) ... thanks

HAVE:

LOC ID PL Other

342 3 A 1d

342 3 A 2f

456 5 B e4

456 6 B d2

645 7 C r5

645 7 D e7

WANT:

LOC old_ID new_ID old_PL new_PL Other

342 3 3 A A 1d

456 5 6 B B e4

645 7 7 C D r5

Keeping the Other variable is not that important, it can be dropped if it complicates the code... Thanks

Re: create 2 new vars a new and old from 2 rows

ballardw — Mon, 07 Jan 2013 20:30:46 GMT

Assuming LOC always has only two values

Data want (keep=loc old_id new_id old_pl new_pl other);

set have;

by LOC unsorted;

old_id=lag (id);

new_id=id;

old_pl = lag (pl);

new_PL = pl;

other = lag(other);

if last.loc;

run;

may do what you need.

Re: create 2 new vars a new and old from 2 rows

PaigeMiller — Mon, 07 Jan 2013 20:32:17 GMT

Assign a variable to the original data set that determines if the row number is odd or even (actually, this produces 0 for even and 1 for odd)

odd_Even=mod(_n_,2);

Then create a new data set by merging the odd rows with the even rows and assign the values to old_id, new_id old_Pl new_PL.

The key part is the merge statement

merge old_dataset(where=(odd_even=0) rename=(id=old_id pl=old_pl)) old_dataset(where=(odd_even=1) rename=(id=new_id pl=new_pl) drop=other);

by loc;

This will work if each LOC has exactly 2 rows. Otherwise, there are modifications you would need to the dataset and program if sometimes LOC can have fewer than or greater than 2 rows.

Re: create 2 new vars a new and old from 2 rows

Astounding — Mon, 07 Jan 2013 20:59:46 GMT

Here's a variation that handles "Other" and gives you a fighting chance if you have 1 (or even 3) records per LOC:

data want;

do i=1 to 2 until (last.LOC);

set have (rename=(other=original_other));

by LOC;
if i=1 then do;

old_ID=ID; old_PL=PL; other=original_other;

end;

else do;

new_ID=ID; new_PL=PL;

end;

drop ID PL original_other;

run;

Good luck.

Re: create 2 new vars a new and old from 2 rows

sam369 — Mon, 07 Jan 2013 21:35:13 GMT

Here the sql apporach

data test;

set test;

by loc notsorted;

if first.loc then var=0;

else var=1;

run;

proc sql;

select a.loc,a.old_id,a.old_pl,a.old_other,b.new_id,b.new_pl,b.new_other

from test(rename=(id=old_id pl=old_pl other=old_other)) as a full join

test(rename=(id=new_id pl=new_pl other=new_other)) as b

on a.loc=b.loc

having a.var=0 & b.var=1;

;

quit;

Regards

Sam

Re: create 2 new vars a new and old from 2 rows

joehinson — Mon, 07 Jan 2013 22:32:00 GMT

...now a hash solution:

data _null_;

length old_ID new_ID 8 old_PL new_PL $4;

if(1=2) then set have;

declare hash w (ordered:"a");

rc=w.defineKey("LOC");

rc=w.defineData("LOC","old_ID","new_ID","old_PL","new_PL","Other");

rc=w.defineDone();

call missing(of _all_);

x=0;

do until(done);

x=x+1;

y=band(1,x);

set have end=done;

if y=1 then do;

OD=ID;

OP=PL;

OTH=Other;

end;

else if y=0 then do;

ND=ID;

NP=PL;

end;

rcw=w.replace(key:LOC,data:LOC,data:OD,data:ND,data:OP,data:NP,data:OTH);

end;

w.output(dataset:"want");

stop;

run;

Re: create 2 new vars a new and old from 2 rows

podarum — Tue, 08 Jan 2013 14:34:15 GMT

Thanks for all the great answers.. I appreciate all the input and help.

Re: create 2 new vars a new and old from 2 rows

podarum — Tue, 08 Jan 2013 17:08:09 GMT

How could I not include rows if it is only a stand alone and has no pairing.. for example

HAVE

LOC ID PL Other

342 3 A 1d

342 3 A 2f

399 2 F 9d

456 5 B e4

456 6 B d2

645 7 C r5

645 7 D e7

738 5 E 8s

WANT:

LOC old_ID new_ID old_PL new_PL Other

342 3 3 A A 1d

456 5 6 B B e4

645 7 7 C D r5

Thanks again..

Re: create 2 new vars a new and old from 2 rows

NickR — Tue, 08 Jan 2013 18:20:03 GMT

Below code should give your desired output.

data want;

set have(rename=(other=other_old));

by loc;

if first.loc then seq=.;

seq+1;

retain old_ID new_ID old_PL new_PL Other;

if seq=1 then do; old_id=id; old_pl=pl; other=other_old; end;

if seq=2 then do; new_id=id; new_pl=pl; output; end;

drop id pl other_old seq;

run;

Re: create 2 new vars a new and old from 2 rows

podarum — Tue, 08 Jan 2013 19:08:35 GMT

Awesome...great stuff... thanks