Help using Base SAS procedures

Merge panel with cross sectional

Reply
Contributor
Posts: 52

Merge panel with cross sectional

Hi

I have a panel dataset with fiscal year end dates. The second dataset is a cross-sectional one with bond issue dates.

Now, I would like to merge the first with the second based on this criterion:

The first would be matched with the second based  on id(cnum and new_cusip) of course. However, the date matching criterion is that the fiscal year end date matches with the first bond issue date that occurs after the fiscal year end date.

I would appreciate any idea. I have attached two sample datasets.

Regards

Namrata

Attachment
Attachment
Contributor
Posts: 52

Re: Merge panel with cross sectional

Any ideas anyone? Smiley Happy

Respected Advisor
Posts: 4,934

Re: Merge panel with cross sectional

Sure, try this:

proc sql;

create table set3 as

select

     a.*,

     b.proceeds,

     b.issue_date

from

    sasforum.set1 as a left join

    (   select

            intnx("YEAR", issue_date, -1, "END") as DATADATE,

            issue_date,

            proceeds,

            newcusip

        from sasforum.set2

        group by newcusip, calculated DATADATE

        having newcusip = min(newcusip)  ) as b

    on a.cnum=b.newcusip and a.DATADATE=b.DATADATE;

quit;

Replace left join with inner join if you want only the matching cases.

PG

PG
Ask a Question
Discussion stats
  • 2 replies
  • 177 views
  • 0 likes
  • 2 in conversation