BookmarkSubscribeRSS Feed
NonSleeper
Quartz | Level 8

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?

4 REPLIES 4
PGStats
Opal | Level 21

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
NonSleeper
Quartz | Level 8

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

PGStats
Opal | Level 21

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
NonSleeper
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 509 views
  • 5 likes
  • 2 in conversation