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 |
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.