topic Re: Merging data from a lookup file to primary file in SAS Programming

Merging data from a lookup file to primary file

danwarags — Tue, 26 Apr 2016 19:55:44 GMT

Hello Everyone,

I have a lookup dataset (X) with 2 variables i.e country and unique ID for every country. I have lot of observations in the table but I pasted only few of them.

Country UID Recode

US 100 USTATES

MEXICO 111 MEX

ARGENTINA 123 ARGEN

JAPAN 145 JAP

INDIA 160 IND

RUSSIA 125 RUSS

I have a Primary dataset (Y) with these variables

Drug Country UID

A US 100

K INDIA 160

L RUSSIA 125

C MEXICO 111

D ARGENTINA 123

M JAPAN 145

Now, by using the lookup table and UID, I need to merge the variable RECODE from look up table (X) to the primary dataset (Y). The output dataset should look like below:

Drug Country UID Recode

A US 100 USTATES

K INDIA 160 IND

L RUSSIA 125 RUSS

C MEXICO 111 MEX

D ARGENTINA 123 ARGEN

M JAPAN 145 JAP

I need help with SAS code on how to do this on larger dataset with 50,000 observations.

Thank you in Advance

Danwa

Re: Merging data from a lookup file to primary file

stat_sas — Tue, 26 Apr 2016 20:10:29 GMT

proc sql;
create table want as
select a.*, b.Recode from Y a
left join X b
on a.UID=b.UID;
quit;

Re: Merging data from a lookup file to primary file

danwarags — Tue, 26 Apr 2016 20:56:35 GMT

Thanks for the solution stat_sas. Could you tell me what does 'a' and 'b' referred to in this code

Re: Merging data from a lookup file to primary file

stat_sas — Tue, 26 Apr 2016 21:14:40 GMT

'a' and 'b' are table aliases. These are just to refer table names. 'a' is refering table Y and 'b' is refering table X.

Re: Merging data from a lookup file to primary file

danwarags — Tue, 26 Apr 2016 22:03:57 GMT

Thank you so much stat_sas. This solution worked for me.

Re: Merging data from a lookup file to primary file

ballardw — Wed, 27 Apr 2016 18:51:24 GMT

@danwarags wrote:
Thanks for the solution stat_sas. Could you tell me what does 'a' and 'b' referred to in this code

To expand slightly on @stat_sas's response.

The code posted used a "short hand" version of referencing the aliases. A more formal code line uses AS to create that reference.

A slightly different code appearance for the same thing:

proc sql;
   create table want as
   select a.*, b.Recode 
   from Y as a left join 
        X as b
      on a.UID=b.UID;
quit;

The aliases make more sense when the tables have names like CountryLookUpTable and CountryDrugReferenceData. Otherwise you get to type those table names a lot with some operations. Typing A. reduces errors from typing CountryLookUpTable multiple times and makes the code a bit easier to read and understand (with practice).

Re: Merging data from a lookup file to primary file

danwarags — Wed, 27 Apr 2016 14:12:48 GMT

Hello Stat_sas,

Thanks for the solution.

If I wanted to join two or more variables to the table 'Y' using UID as reference, then how would we code it. Guide me.

For example,

For dataset 'X' the variables are,

Country UID Recode Population Region

US 100 USTATES 12345 Texas

MEXICO 111 MEX 14567 International

ARGENTINA 123 ARGEN 15678 International

JAPAN 145 JAP 12345 Asia

INDIA 160 IND 14569 Asia

RUSSIA 125 RUSS 23567 Asia

For dataset "Y" the variables are

Drug Country UID

A US 100

K INDIA 160

L RUSSIA 125

C MEXICO 111

D ARGENTINA 123

M JAPAN 145

My output dataset should look like this using UID as reference:

Drug Country UID Recode Population Region

A US 100 USTATES 12345 Texas

K MEXICO 111 MEX 14567 International

L ARGENTINA 123 ARGEN 15678 International

C JAPAN 145 JAP 12345 Asia

D INDIA 160 IND 14569 Asia

M RUSSIA 125 RUSS 23567 Asia

Re: Merging data from a lookup file to primary file

stat_sas — Wed, 27 Apr 2016 16:57:35 GMT

This can be achieved by adding two more fields from X data set. As 'b' is refering table X, so if you need to add more fields from table X just add table reference (b) along with field name. Some thing like this should work

proc sql;
create table want as
select a.*, b.Recode, b.Population, b.Region from Y a
left join X b
on a.UID=b.UID;
quit;