topic Re: merging by identifying the most recent date relative to the refernce date in SAS Programming

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

aarony — Mon, 02 Jan 2017 17:39:12 GMT

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.

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

aarony — Mon, 02 Jan 2017 18:12:08 GMT

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?

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

Tom — Mon, 02 Jan 2017 18:41:19 GMT

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.

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

aarony — Mon, 02 Jan 2017 18:44:06 GMT

Thanks so much for your guidance. I am super new to this. Do u mind sharing how to do the left join??

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

Tom — Mon, 02 Jan 2017 18:59:11 GMT

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;

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

aarony — Mon, 02 Jan 2017 22:52:06 GMT

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

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

Tom — Mon, 02 Jan 2017 23:20:43 GMT

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.

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

aarony — Mon, 02 Jan 2017 23:21:30 GMT

thank you so somuch!!! 🙂

this was very very helpful!

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

Ksharp — Tue, 03 Jan 2017 03:12:40 GMT

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;