BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aarony
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
aarony
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

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.

aarony
Obsidian | Level 7
Thanks so much for your guidance. I am super new to this. Do u mind sharing how to do the left join??
Tom
Super User Tom
Super User

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;
aarony
Obsidian | Level 7
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
Tom
Super User Tom
Super User

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.

aarony
Obsidian | Level 7

thank you so somuch!!! 🙂

 

this was very very helpful!

Ksharp
Super User
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 2528 views
  • 0 likes
  • 3 in conversation