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.
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.
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?
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.
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;
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.
thank you so somuch!!! 🙂
this was very very helpful!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.