topic Re: How to merge daily and quarterly dataset? in SAS Programming

How to merge daily and quarterly dataset?

ZZB — Sun, 07 Jan 2018 08:07:56 GMT

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;

Re: How to merge daily and quarterly dataset?

ZZB — Sun, 07 Jan 2018 08:11:57 GMT

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

Re: How to merge daily and quarterly dataset?

mkeintz — Sun, 07 Jan 2018 15:27:26 GMT

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.

Re: How to merge daily and quarterly dataset?

PeterClemmensen — Sun, 07 Jan 2018 15:43:44 GMT

See my answer to your other thread.

Also see Combining Time Series with Different Frequencies from the PROC EXPAND documentation