Help using Base SAS procedures

merging with a condition

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

merging with a condition

Hi everyone, I have a merging question.  I have two datasets that I want to merge using an identifier and year.  But I need to include a condition that if the year is not present in the second dataset, I would like to use the previous year's data entry.  Here are examples of the data:

Data 1:

Firm      Year

1004     1996

1160     1996

1235     1996

Data 2:

Firm     Year     RepMeasure

1004     1995     0.36

1004     1996     0.42

1160     1995     0.11

1235     1995     0.78

1235     1996     0.69

For Firms 1004 and 1235, merging by firm and year is fine. For firm 1160 though, I don't have 1996 data so I need to use the 1995 RepMeasure.  Let me know if I need to provide any more info. Thanks for your help!


Accepted Solutions
Solution
‎12-13-2013 01:32 PM
Respected Advisor
Posts: 3,156

Re: merging with a condition

Performancewise, data step hash probably better, but Proc SQL can offer a simple syntax: 

Data _1;

input Firm$      Year;

cards;

1004     1996

1160     1996

1235     1996

;

Data _2;

input Firm$     Year     RepMeasure;

cards;

1004     1995 0.36

1004     1996 0.42

1160     1995 0.11

1235     1995 0.78

1235     1996 0.69

;

proc sql;

  create table _merge as

     select a.*, repmeasure

       from _1 a

         left join _2 b

                   on a.firm=b.firm

                   and a.year - b.year in (1,0)

         group by a.firm

     having a.year - b.year = min(a.year-b.year)

      ;

      quit;

                   

        

Haikuo

View solution in original post


All Replies
Super Contributor
Posts: 253

Re: merging with a condition

Easiest way might be just to take the Data2 dataset and add rows for missing years, then merge.

Solution
‎12-13-2013 01:32 PM
Respected Advisor
Posts: 3,156

Re: merging with a condition

Performancewise, data step hash probably better, but Proc SQL can offer a simple syntax: 

Data _1;

input Firm$      Year;

cards;

1004     1996

1160     1996

1235     1996

;

Data _2;

input Firm$     Year     RepMeasure;

cards;

1004     1995 0.36

1004     1996 0.42

1160     1995 0.11

1235     1995 0.78

1235     1996 0.69

;

proc sql;

  create table _merge as

     select a.*, repmeasure

       from _1 a

         left join _2 b

                   on a.firm=b.firm

                   and a.year - b.year in (1,0)

         group by a.firm

     having a.year - b.year = min(a.year-b.year)

      ;

      quit;

                   

        

Haikuo

Occasional Contributor
Posts: 18

Re: merging with a condition

Hi Sir,

I would like to seek clarification on the logic /*and a.year - b.year in (1,0)*/ as it seems the program returns the same results without the condition. So is that condition written to make sure to avoid other possible values? I'd appreciate if you can correct my understanding

sql;


create table _merge as


select a.*, repmeasure


from _1 a


left join _2 b


on a.firm=b.firm


/* and a.year - b.year in (1,0)*/


group by a.firm


a.year - b.year = min(a.year-b.year);


;



Respected Advisor
Posts: 3,156

Re: merging with a condition

Posted in reply to NaveenSrinivasan

Yes, it is there to rule out other possibilities.  In your initial question, you asked to use "previous year" in case of no-match. Consider the following slightly modified data, run the code with and without the additional condition.

Data _2;

input Firm$ Year RepMeasure;

cards;

1004 1995 0.36

1004 1996 0.42

1160 1995 0.11

1160 1997 100

1235 1995 0.78

1235 1996 0.69

;

Haikuo

Super User
Posts: 5,518

Re: merging with a condition

Two key questions ...

1. Can we rely on this being the full set of all variables in the two incoming data sets?

2. Which observations do you want in the output?  There are at least two ways to imagine what the results should look like.

Contributor
Posts: 23

Re: merging with a condition

Hai.kuo that worked well. Thanks!

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 261 views
  • 0 likes
  • 5 in conversation