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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.