Attaching variable from another dataset

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Attaching variable from another dataset

Dear All,

I have the following two datasets (both sorted with respect to the variables id and date):

data db1;

     input id date V1 V2;

datalines;

1     20110902     0.5     2.1

2     20110903     0.7     2.8

;

data db2;

     input id date V3;

datalines;

1     20110901     10

1     20110902     20

1     20110903     30

2     20110901     60

2     20110902     50

2     20110903     40

;


I would like to obtain the following dataset db3:


data db3;

     input id date V1 V2 V3;

datalines;

1     20110902     0.5     2.1     20

2     20110903     0.7     2.8     40

;

That is, I would like to add the variable V3 to the first dataset db1 when the values of the variables ID and DATE in db1 coincide with those in db2. Hence, the third dataset must have the size of the first one.

I have done two attempts. The first one involves PROC SQL

proc sql;

  create table db3

  as select db1.*, db2.V3

  from db1, db2

  where db1.id = db2.id and db1.date = db2.date;

quit;


The second one involves a simple merge with respect to both key variables

data db3;

  merge db1 db2;

  by id date;

run;


Any help would be highly appreciated.



Accepted Solutions
Solution
‎09-02-2014 12:59 PM
Trusted Advisor
Posts: 1,202

Re: Attaching variable from another dataset

Not sure, may be not understood properly. Your own syntax for sql can generate what you are trying to get.

proc sql;

  create table db3

  as select db1.*, db2.V3

  from db1, db2

  where db1.id = db2.id and db1.date = db2.date;

quit;

View solution in original post


All Replies
Super User
Posts: 1,098

Re: Attaching variable from another dataset

Please try

data db3;

  merge db1(in=a) db2(in=b);

  by id date;

if a;

run;

Thanks,
Jag

Thanks,
Jag
Grand Advisor
Posts: 10,194

Re: Attaching variable from another dataset

And an SQL

proc sql;

  create table db3

  as select db1.*, db2.V3

  from db1 left join db2

  on db1.id = db2.id and db1.date = db2.date;

quit;

Solution
‎09-02-2014 12:59 PM
Trusted Advisor
Posts: 1,202

Re: Attaching variable from another dataset

Not sure, may be not understood properly. Your own syntax for sql can generate what you are trying to get.

proc sql;

  create table db3

  as select db1.*, db2.V3

  from db1, db2

  where db1.id = db2.id and db1.date = db2.date;

quit;

Contributor
Posts: 35

Re: Attaching variable from another dataset

Thank you guys for all your kind replies.

Unfortunately, your solution doesn't allow me to get the desired result. I have found a potential solution in this discussion where you replied years ago:

data db3;

merge db1 db2;

by id date;

if not missing (V1) then output;

call missing(V1);

run;

, I obtain the number of rows of db2, instead of db1.

@stat@sas You are right. This solution works for my reproducible example. However, it does not for my real dataset.

Super User
Posts: 1,098

Re: Attaching variable from another dataset

Hi Mark,

Thanks for your response

i just checked the code i suggested to you and i see it is producing your expected output. like it is generating the same number of observations similar to db1 by matching on id and date variables. could you please again check and correct me.

data db1;

     input id date V1 V2;

datalines;

1     20110902     0.5     2.1

2     20110903     0.7     2.8

;

data db2;

     input id date V3;

datalines;

1     20110901     10

1     20110902     20

1     20110903     30

2     20110901     60

2     20110902     50

2     20110903     40

;

proc sort data=db1;

by id date;

run;

proc sort data=db2;

by id date;

run;

data db3;

  merge db1(in=a) db2(in=b);

  by id date;

if a;

run;

Thanks,

Jag

Thanks,
Jag
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 326 views
  • 6 likes
  • 4 in conversation