Programming the statistical procedures from SAS

join two tables with different dates (one reporting date and another running date) on an ID variable

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

join two tables with different dates (one reporting date and another running date) on an ID variable

[ Edited ]

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!

 


Accepted Solutions
Solution
‎04-10-2017 05:16 AM
Esteemed Advisor
Posts: 7,056

Re: join two tables with different dates (one reporting date and another running date) on an ID vari

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

 

View solution in original post


All Replies
Solution
‎04-10-2017 05:16 AM
Esteemed Advisor
Posts: 7,056

Re: join two tables with different dates (one reporting date and another running date) on an ID vari

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 66 views
  • 0 likes
  • 2 in conversation