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

Reply
Frequent Contributor
Posts: 75

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

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?

Respected Advisor
Posts: 4,925

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

Posted in reply to NonSleeper

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

PG
Frequent Contributor
Posts: 75

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

Posted in reply to NonSleeper

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

Respected Advisor
Posts: 4,925

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

Posted in reply to NonSleeper

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

PG
Frequent Contributor
Posts: 75

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

Posted in reply to NonSleeper

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;

Ask a Question
Discussion stats
  • 4 replies
  • 223 views
  • 5 likes
  • 2 in conversation