The hypothetical data looks like this:
data temp;
input ID1 ID2 VarID1;
datalines;
1 2 11
1 3 11
1 4 11
2 1 12
2 4 12
3 5 13
4 1 14
5 4 .
;
run;
Now I want to make a new data set that looks like this:
ID1 ID2 VarID1 VarID2;
1 2 11 12
1 3 11 13
1 4 11 14
2 1 12 11
2 4 12 14
3 5 13 .
4 1 14 11
5 4 . 14
The idea is: Contrast ID1 and ID2, if ID2 is equal to ID1 (in fact, this means the same person) then VarID2 is equal to VarID1 at that ID1 value. For example, if ID2=2, then VarID2=12. How can I do such maneuver?
This way :
proc sql;
create table want as
select a.*, b.VarID1 as VarID2
from
temp as a inner join
(select unique ID1, VarID1 from temp) as b on a.ID2=b.ID1;
select * from want;
quit;
PG
Thanks for your help. Is there some different, more rudimentary way, as I'm not familiar with sql stuffs?
I run the program yet the results seem not as expected. Here's what the table want looks like
ID1 ID2 VarID1 VarID2
1 2 10 13
1 2 10 14
1 3 11 15
1 4 12 16
2 1 13 10
2 1 13 12
2 1 13 11
3 2 15 13
3 2 15 14
With your original example data, I get the result :
ID1 ID2 VarID1 VarID2
----------------------------------------------
1 2 11 12
1 3 11 13
1 4 11 14
2 1 12 11
2 4 12 14
3 5 13 .
4 1 14 11
5 4 . 14
which matches your original request.
PG
I run this rudimentary programming and looks like it does the same.
data temp2;
set temp;
drop ID2;
rename ID1=ID2 VarID1=VarID2;
run;
proc sort data=temp;
by ID2;
run;
proc sort data=temp2;
by ID2;
run;
data temp3;
merge temp temp2;
by ID2;
run;
proc sort data=temp3 nodupkey;
by ID1 ID2;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.