merging by identifying the most recent date relative to the refernce date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

merging by identifying the most recent date relative to the refernce date

i have a dataset (dataset1).

ticker date            value

a        19991231     1

a        20011231     2

a        20031231     3

 

i have another dataset (dataset2).

ticker date

a         20020101

 

i want to find the most recent date from dataset 1 in reference to dataset 2. (ie., find in the dataset1 the closest recent date to the date in dataset2 to rrive at the following):

ticker date             value

a         20020101       2

 

could you provide me of your guidance? thx in advance.


Accepted Solutions
Solution
‎01-02-2017 06:21 PM
Super User
Super User
Posts: 6,842

Re: merging by identifying the most recent date relative to the refernce date

Make sure your variables are really dates.  They might be datetime values with format that makes them look like dates. Or since you are not printing any delimiters it is possible your date variables as just numbers that look to you like a date but that SAS is think is just a number.

View solution in original post


All Replies
Frequent Contributor
Posts: 102

Re: merging by identifying the most recent date relative to the refernce date

i am trying something like:

 

proc sql;
create table f32 as select
a.*, b.*
from m2 as a, t3 as b
where a.ticker=b.ticker and b.date-a.date=min(b.date-a.date);
quit;

 

but this does not work. any one has any insights?

Super User
Super User
Posts: 6,842

Re: merging by identifying the most recent date relative to the refernce date

[ Edited ]

You do not have GROUP BY clause.  Also you want to use a HAVING clause to subset using the aggregate value.

 

Try something like this:

 

proc sql;
  create table f32 as
    select a.*
         , b.*
         , (b.date - a.date) as diff
    from m2 as a
       , t3 as b
    where a.ticker=b.ticker
      and a.date <= b.date 
    group by a.ticker
    having (calculated diff)=min(calculated diff)
  ;
quit;

 

You might also what to use an LEFT JOIN instead of the implied inner join so that you can keep records that do not have prior dates to match to them.

Frequent Contributor
Posts: 102

Re: merging by identifying the most recent date relative to the refernce date

Thanks so much for your guidance. I am super new to this. Do u mind sharing how to do the left join??
Super User
Super User
Posts: 6,842

Re: merging by identifying the most recent date relative to the refernce date

[ Edited ]

Let's make some example data.

data lookup;
 input ticker $ date value ;
 informat date yymmdd10.;
 format date yymmdd10. ;
cards;
a 19991231 1
a 20011231 2
a 20031231 3
b 20151231 4
;

data master;
  input ticker $ date ;
  informat date yymmdd10.;
  format date yymmdd10.;
cards;
a 20020101
c 20160701
;

Now let's use LOOKUP to find the most recent VALUE for each record in the MASTER table.  Notice that the TICKER='c' there will be no match.

proc sql;
  create table want as
    select master.*
         , lookup.value
         , lookup.date as value_date
    from master
    left join lookup
    on master.ticker=lookup.ticker
      and lookup.date <= master.date
    group by master.ticker
    having (lookup.date)=max(lookup.date)
  ;
quit;
Frequent Contributor
Posts: 102

Re: merging by identifying the most recent date relative to the refernce date

Dear Tom, thank you so much for your kind guidance.
For some reason, the code is not grabbing the value from most recent date. I am really puzzled why this is.
Could it be because of the date format? My date format is YYMMDDN8.
I get crazy numbers on my DIFF variable...i.e., 20112614
Solution
‎01-02-2017 06:21 PM
Super User
Super User
Posts: 6,842

Re: merging by identifying the most recent date relative to the refernce date

Make sure your variables are really dates.  They might be datetime values with format that makes them look like dates. Or since you are not printing any delimiters it is possible your date variables as just numbers that look to you like a date but that SAS is think is just a number.

Frequent Contributor
Posts: 102

Re: merging by identifying the most recent date relative to the refernce date

thank you so somuch!!! Smiley Happy

 

this was very very helpful!

Super User
Posts: 9,865

Re: merging by identifying the most recent date relative to the refernce date

data lookup;
 input ticker $ date value ;
 informat date yymmdd10.;
 format date yymmdd10. ;
cards;
a 19991231 1
a 20011231 2
a 20031231 3
b 20151231 4
;

data master;
  input ticker $ date ;
  informat date yymmdd10.;
  format date yymmdd10.;
cards;
a 20020101
c 20160701
;
data want;
 set lookup(in=ina) master(in=inb);
 by ticker date;
 retain temp;
 if first.ticker then temp=.;
 if ina then temp=value;
 if inb then do;value=temp;output;end;
 drop temp;
run;
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 198 views
  • 0 likes
  • 3 in conversation