Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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);
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;
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.;
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
                Apr 21, 2017 at 18:16
                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
                Apr 22, 2017 at 17:40
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
data data2;
input date2 value2;
informat date2 yymmn6.;
format date2 yymmn6.;
cards;
200101 3000000
200102 4000000
200301 2000000
200212 2000000
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.