topic Re: merging monthly data and daily to daily data in SAS Programming

merging monthly data and daily to daily data

JKCho — Wed, 10 Jul 2019 20:35:06 GMT

Hello,

I merged two(monthly and daily) and made a merged daily one but the daily one is not like what I want...

I used a merge code as below.

data CRSP_new;
set CRSP1;
year = YEAR(date);
run;

proc sql;
create table crspcomp as
select a.* , b.*
from cstatcusip1 a
inner join CRSP_new b
on a.cusip8 = b.cusip and a.fyear= b.year;
quit;

and... it made this

What I rather wanted to have is no double dates on NamesDate row in chronological order and the same in I/B/E/S row too.

As seen below, IBES is a monthly data set and it has median values that need to be matched by CUSIP and date.

My problem is... medians need to be changed when corresponding I/B/E/S dates change.

This seems kind of advanced than I usually deal with and cannot be done by the above code as this time needs chronological order + corresponding changes in median values. I referred to some related threads but failed to make it. and finally asking for your help again...

Please share some advice again

I sincerely appreciate all of your help!!

Jerry

Re: merging monthly data and daily to daily data

ballardw — Wed, 10 Jul 2019 22:05:17 GMT

Examples of the input data would be helpful. You have hidden A LOT by not listing variable names as a minimum and likely cutting out much of the result.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Examples from BOTH of your INPUT data sets are needed.

Pictures of data are very hard to write code against.

It also appears that you need to match something by an actual date value, not the YEAR.

Re: merging monthly data and daily to daily data

ChrisNZ — Wed, 10 Jul 2019 22:30:20 GMT

Also show your expected output.

Re: merging monthly data and daily to daily data

JKCho — Thu, 11 Jul 2019 02:50:24 GMT

Somehow I solved the issue and thank for your help I will attach data code instead of pics later!