topic Re: Merge panel with cross sectional in SAS Procedures

Merge panel with cross sectional

namrata — Tue, 25 Nov 2014 23:48:12 GMT

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

Re: Merge panel with cross sectional

namrata — Fri, 05 Dec 2014 00:09:06 GMT

Any ideas anyone?

Re: Merge panel with cross sectional

PGStats — Fri, 05 Dec 2014 03:38:26 GMT

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.