BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ZZB
Obsidian | Level 7 ZZB
Obsidian | Level 7

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
ZZB
Obsidian | Level 7 ZZB
Obsidian | Level 7

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

 

 

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PeterClemmensen
Tourmaline | Level 20

See my answer to your other thread.

 

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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