proc sql left join
Posted: 08 February 2013 09:44 AM   [ Ignore ]
Total Posts:  22
Joined  2013-01-15


I use the following code to add some variables from data set b to data set a. suppose a has a large number of variables I want to keep in the merged data set, b has a few variables I want to add to the merged one.

when I ran the code, I got a warning, “WARNING: Variable CUSIP already exists on file WORK.COMB.”

my question is:
1. what is most efficient way of selecting variables , do I have to write “a.var1, a.var2, .... b.var1, ” in the select statement

2. could you show an example or two.

Thx !!!

PROC SQL;         
  create table comb as
  select *

  from ibes as a left join perm as b
  on a.CUSIP=b.CUSIP;

Posted: 08 February 2013 09:04 PM   [ Ignore ]   [ # 1 ]
Total Posts:  901
Joined  2011-09-19

hi R,

In this case, you want to join a set with many variables (‘a’) with a set with a few variables (‘b’). In that case, the code you have will work fine. There is a warning because variable CUSIP exists in both a and b, so SAS tries to add it twice. This will not work (and is no problem) because a variable name needs to be unique. (So in this case you can ignore the warning).

If b would have many variables, you can do two things:
- explicitly state which variables you want, for example: select a.*, b.var1, b.var2, b.var3
- use ‘keep’, example: create table comb (keep = fyear datadate sale at var1 var2 vars) as select * from ...

The first method is fastest to execute; with large datasets it will only collect the variables var1 var2 var3 from b, and not all the variables in b.
The second method is fastest to type, but slower to execute: all variables in b are initially read, and later on only the ones in ‘keep’ are kept.

By the way, there is also a ‘drop = ..’ option, where you specify which variables to drop.

Hope this helps,



To reply/post new questions: Please use the group WRDS/SAS on Google Groups!

Posted: 10 February 2013 10:40 AM   [ Ignore ]   [ # 2 ]
Total Posts:  22
Joined  2013-01-15

Thanks Joost!