Hello,
I have two tables, table A (including firm ID and the date it releases the earnings number, only once a year) and table B (including the date of business news releases related to each firm, many times a year).
Table A
firm_ID earnings announcement date (rdq)
1 20110330
1 20120330
1 20130330
2 20100225
2 20110225
2 20120225
Table B
firm_ID date of news release (news)
1 20101101
1 20110202
1 20110331
1 20110731
1 20120428
1 20130225
2 20090220
2 20100218
2 20100425
2 20110707
I would like to merge both tables in a table C that includes all the "news" up to the following "rdq" date. The expected output would be as follows:
Table C
firm_ID date of news release (news) earnings announcement date (rdq)
1 20101101 20110330
1 20110202 20110330
1 20110331 20120330 /*March 31st news fall in the following rdq date*/
1 20110731 20120330
1 20120428 20130330
1 20130225 20130330
2 20090220 20100225
2 20100218 20100225 /*Feb 18th news still falls in the current rdq date*/
2 20100425 20110225
2 20110707 20120225
Any help would be really much appreciated. Thanks a lot!
I think that the following meets your conditions:
proc sql; create table want as select a.firm_ID, a.rdq, b.news from TableA a left join TableB b on a.firm_ID eq b.firm_ID where b.news le a.rdq group by b.firm_ID,b.news having a.rdq-b.news eq min(a.rdq-b.news) order by a.firm_ID,b.news ; quit;
Art, CEO, AnalystFinder.com
I think that the following meets your conditions:
proc sql; create table want as select a.firm_ID, a.rdq, b.news from TableA a left join TableB b on a.firm_ID eq b.firm_ID where b.news le a.rdq group by b.firm_ID,b.news having a.rdq-b.news eq min(a.rdq-b.news) order by a.firm_ID,b.news ; quit;
Art, CEO, AnalystFinder.com
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.