matching two datasets with one month lag

Question

I am trying to match max daily data within a month to a monthly data.

data daily;
input permno $ date ret;
datalines;
1000 19860101 88
1000 19860102 90
1000 19860201 70
1000 19860202 55
1001 19860201 97 
1001 19860202 74
1001 19860203 79
1002 19860301 55 
1002 19860302 100
1002 19860301 10
;
run; 

data monthly;
input permno $ date ret;
datalines;
1000 19860131 1
1000 19860228 2
1000 19860331 5
1001 19860331 3 
1002 19860430 4
;
run;

The result I want is the following; (I want to match daily max data to one month lag monthly data. )

1000 19860102 90 1000 19860228 2
1000 19860201 70 1000 19860331 5
1001 19860201 97 1001 19860331 3
1002 19860302 100 1002 19860430 4

Below is what I have tried so far. I want to have maximum ret value within a month so I have created yrmon to assign same yyyymm data for the same month daily data

data a1; set daily;
yrmon=year(date)*100 + month(date);
run;

In order to choose the maximum value(here, ret) within same yrmon group for the same permno, I used code below

proc means data=a1 noprint;
class permno yrmon ;
var ret;
output out= a2 max=maxret;
run;

However, it only got me permno yrmon ret data, leaving the original date data away.

data a3;
set a2;
new=intnx('month',yrmon,1);
format date new yymmn6.;
run;

But it won't work since yrmon is no longer date format.

Thank you in advance.

Hello

I am trying to match two different sets by permno(same company) but with one month lag (eg. daily9 dataset yrmon=198601 and monthly2 dataset yrmon=198602) it is pretty difficult to handle for me because if I just add +1 in yrmon, 198612 +1 will not be 198701 and I am confused with handling these issues.

Can anyone help?

Are your dates numeric with a date format, numeric, or character? You should post data as a sample and what you've tried as well. You can do this in the join condition and use INTNX() to increment the month if this is a SAS date. — Reeza
my data is in date format(yyyymmdd) but using data data3; set data2; yrmon=year(date)*100 + month(date); run; I got the data I wanted but it became numeric data — Sunyoung Lee

G.Arima G.Arima · Accepted Answer · 2017-04-21T20:09:26

1) informat date1/date2 yymmn6. is used to read the date in yyyymm format
2) format date1/date2 yymmn6. is used to view the date in yyyymm format
3) intnx("months",b.date2,-1) is used to join the dates with lag of 1 month

data data1;
input date1 value1;
informat date1 yymmn6.;
format date1 yymmn6.;
cards;
200101 200
200212 300
200211 400
;
run;

data data2;
input date2 value2;
informat date2 yymmn6.;
format date2 yymmn6.;
cards;
200101 3000000
200102 4000000
200301 2000000
200212 2000000
;
run;


proc sql;
create table result as 
select a.*,b.date2,b.value2 from
data1 a
left join
data2 b
on a.date1 = intnx("months",b.date2,-1);
quit;

My Output:

date1   |value1 |date2  |value2
200101  |200    |200102 |4000000
200211  |400    |200212 |2000000
200212  |300    |200301 |2000000

Let me know in case of any queries.

matching two datasets with one month lag

1 Answers