BookmarkSubscribeRSS Feed
namrata
Fluorite | Level 6

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

2 REPLIES 2
namrata
Fluorite | Level 6

Any ideas anyone? Smiley Happy

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 821 views
  • 0 likes
  • 2 in conversation