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
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.