How to merge daily and quarterly dataset?

Accepted Solution Solved
Reply
Contributor ZZB
Contributor
Posts: 43
Accepted Solution

How to merge daily and quarterly dataset?

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;

 

 

 


Accepted Solutions
Solution
‎01-07-2018 01:53 PM
Trusted Advisor
Posts: 1,312

Re: How to merge daily and quarterly dataset?

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.

View solution in original post


All Replies
Contributor ZZB
Contributor
Posts: 43

Re: How to merge daily and quarterly dataset?

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

 

 

Solution
‎01-07-2018 01:53 PM
Trusted Advisor
Posts: 1,312

Re: How to merge daily and quarterly dataset?

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.

PROC Star
Posts: 1,218

Re: How to merge daily and quarterly dataset?

See my answer to your other thread.

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 179 views
  • 1 like
  • 3 in conversation