I want to match the RDQ (which is date) in the quarterly dataset with the DATE in the daily dateset.
In the daily dataset, there are variables: ID, Date
In the quarterly dataset, variables: ID, RDQ, V1
01 2000/2/14 5
01 2000/5/13 8
01 2000/8/15 7
For example, for a specific two RDQ date, If the date in the daily set is between 2000/2/14 and 2000/5/13, I want to add the V1 (5 in this case) to the daily set for ID with date between 2000/2/15 and 2000/5/13.
How to revise the following codes?
proc sql;
create table one
as select distinct a.*, b.*
from daily as a
left join quarterly as b
on a.ID=b.lD and ???;
quit;
RDQ? You must be dealing with earning report date, from Compustat. If so, I guess the identifier is GVKEY. In that case, you can do this:
data want;
set qtr (keep=gvkey rdq rename=(rdq=date) in=inq)
daily (in=ind);
by gvkey date;
if first.gvkey then call missing(v1);
if inq then set qtr (keep=rdq v1);
if ind;
run;
This program assumes that dataset QTR is sorted by gvkey/rdq, and dataset daily sorted by gvkey/date.
It's important that only GVKEY and RDQ appear in the "keep=" parameter in the first SET statement. Other quarterly variables of interest should appear in the second SET statement. They will be automatically retained until the next instance of a quarterly record.
What I want to get:
Daily dateset:
ID DATE V1
01 2000/2/13 .
01 2000/2/14 .
01 2000/2/15 5
01 2000/2/16 5
.... in between all V1 is 5
01 2000/5/13 5
01 2000/5/14 8
and so on
RDQ? You must be dealing with earning report date, from Compustat. If so, I guess the identifier is GVKEY. In that case, you can do this:
data want;
set qtr (keep=gvkey rdq rename=(rdq=date) in=inq)
daily (in=ind);
by gvkey date;
if first.gvkey then call missing(v1);
if inq then set qtr (keep=rdq v1);
if ind;
run;
This program assumes that dataset QTR is sorted by gvkey/rdq, and dataset daily sorted by gvkey/date.
It's important that only GVKEY and RDQ appear in the "keep=" parameter in the first SET statement. Other quarterly variables of interest should appear in the second SET statement. They will be automatically retained until the next instance of a quarterly record.
See my answer to your other thread.
Also see Combining Time Series with Different Frequencies from the PROC EXPAND documentation
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.