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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.