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
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.
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.
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.
Thanks for all the great answers.. I appreciate all the input and help.
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..
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;
Awesome...great stuff... thanks
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
...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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.