topic Re: How to match two ID variables and create a new response variable? in SAS Programming

How to match two ID variables and create a new response variable?

NonSleeper — Sat, 13 Jul 2013 01:44:53 GMT

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?

Re: How to match two ID variables and create a new response variable?

PGStats — Sat, 13 Jul 2013 02:29:50 GMT

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;

Re: How to match two ID variables and create a new response variable?

NonSleeper — Mon, 15 Jul 2013 06:17:09 GMT

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

Re: How to match two ID variables and create a new response variable?

PGStats — Mon, 15 Jul 2013 12:22:45 GMT

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.

Re: How to match two ID variables and create a new response variable?

NonSleeper — Tue, 16 Jul 2013 01:30:19 GMT

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;