Problem with merging datasets
Posted: 25 December 2014 11:26 AM   [ Ignore ]
Newbie
Rank
Total Posts:  10
Joined  2014-11-10

Hey.

I have three datasets with 3 variables in each - (Ticker Year x/y/z).

The datasets are off different sizes.

I want to make one dataset (Ticker Year x y z) and only keep the rows that contains values in x y and z for the same ticker and year.

I tried the following, but it does not give me the right result, several observations that satisfy my criterias are left out:

data data.want;
merge
          data.ibes_april_zeroscore_clean_null (in = b)
          data.ibes_april_zeroscore_clean_one (in = m)
          data.ibes_april_zeroscore_clean_two (in = t);

if b & m & t;

run;


Ticker Year   Value                 Ticker Year Value           Ticker Year Value           OUTPUT ==>    Ticker   Year   X   Y   Z
    AA   1990   X1                     AA   1990 Y1             AA     1990 Z1                               AA   1990   X1 Y1 Z1
    AA   1991   X2                     AA   1991 Y2
    AA   1992   X3

Simple example, i have a lot of observations for each ticker in different years, but the Ticker-YEAR combinations are not the same in each dataset.

Best regards
Jamil.

 

 

 

Profile
 
 
Posted: 25 December 2014 09:23 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Jamil,

How about proc sql? (Data with merge should be possible as well—I tend to use proc sql though).

proc sql;
 
create table z as select
  x
.*,
  
y.*,
  
z.*
  
from xyz
  where x
.ticker y.ticker and x.year y.year
  
and y.ticker z.ticker and y.year z.year;
quit

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile