## create 2 new vars a new and old from 2 rows

Super Contributor
Posts: 409

# create 2 new vars a new and old from 2 rows

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

Super User
Posts: 13,542

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

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.

Posts: 3,019

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

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.

--
Paige Miller
Super User
Posts: 6,775

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

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;

end;

drop ID PL original_other;

run;

Good luck.

Super Contributor
Posts: 409

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

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

Super Contributor
Posts: 409

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

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

Frequent Contributor
Posts: 81

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

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;

Super Contributor
Posts: 409

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

Awesome...great stuff... thanks

Regular Contributor
Posts: 171

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

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

Contributor
Posts: 45

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

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

end;

w.output(dataset:"want");

stop;

run;

Discussion stats
• 9 replies
• 282 views
• 6 likes
• 7 in conversation