Merge panel with cross sectional

[namrata]

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

[namrata]

Any ideas anyone?

[PGStats]

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