Hi,
My data looks like the first two variables i.e permno and date and I want to create a third variable winID as follows,it can be thought of as non-overlapping rolling windows but then again the size of the window is dependent on the number of days in a month and that is why I am not getting how to handle it.
PERMNO | DATE | WINID |
1 | 1-Jan-00 | 1 |
… | … | 1 |
1 | 31-Jan-00 | 1 |
1 | 1-Feb-00 | 2 |
… | …. | 2 |
1 | 28-Feb-00 | 2 |
2 | 1-Jan-00 | 3 |
… | … | 3 |
2 | 31-Jan-00 | 3 |
2 | 1-Feb-00 | 4 |
… | …. | 4 |
2 | 28-Feb-00 | 4 |
Thanks.
Just test if the current month is different than the previous month.
data want ;
set have ;
by permno date;
winid + (month(date) ne month(lag(date)));
run;
If you could potentially skip exactly 11 months then you might want to make it more complicated and use the INTNX() function instead.
winid + (intnx('month',date,0.'b') ne intnx('month',lag(date),0,'b'));
I am unsure I fully understand. Try:
WINID + ( dif(DATE) ne 0 | dif(PERMO) ne 0 );
Just test if the current month is different than the previous month.
data want ;
set have ;
by permno date;
winid + (month(date) ne month(lag(date)));
run;
If you could potentially skip exactly 11 months then you might want to make it more complicated and use the INTNX() function instead.
winid + (intnx('month',date,0.'b') ne intnx('month',lag(date),0,'b'));
Thanks Tom, this solves my issue.
Hi,
Can you please also suggest how to create a third variable say TIME that represents each day through 1jan00 to 31jan00 as just one month that is jan00 ?
PERMNO | DATE | WINID | TIME |
1 | 1-Jan-00 | 1 | Jan-00 |
… | … | 1 | Jan-00 |
1 | 31-Jan-00 | 1 | Jan-00 |
1 | 1-Feb-00 | 2 | Feb-00 |
… | …. | 2 | Feb-00 |
1 | 28-Feb-00 | 2 | Feb-00 |
2 | 1-Jan-00 | 3 | Jan-00 |
… | … | 3 | Jan-00 |
2 | 31-Jan-00 | 3 | Jan-00 |
2 | 1-Feb-00 | 4 | Feb-00 |
… | …. | 4 | Feb-00 |
2 | 28-Feb-00 | 4 | Feb-00 |
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.