topic Re: Attaching variable from another dataset in SAS Programming

Attaching variable from another dataset

mark_ph — Tue, 02 Sep 2014 15:53:25 GMT

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.

Re: Attaching variable from another dataset

Jagadishkatam — Tue, 02 Sep 2014 15:55:24 GMT

Please try

data db3;

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

by id date;

if a;

run;

Thanks,
Jag

Re: Attaching variable from another dataset

ballardw — Tue, 02 Sep 2014 16:47:17 GMT

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;

Re: Attaching variable from another dataset

stat_sas — Tue, 02 Sep 2014 16:59:16 GMT

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;

Re: Attaching variable from another dataset

mark_ph — Thu, 04 Sep 2014 13:10:56 GMT

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.

Re: Attaching variable from another dataset

Jagadishkatam — Thu, 04 Sep 2014 15:59:13 GMT

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