Hi,
I have the following data with first two variables only i.e permno and date.
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 |
I created the third variable winid using the following code as suggested by one of the community members. The variable winid is dependent on the number of days in a month and is created using the following code,
winid + (intnx('month',date,0.'b') ne intnx('month',lag(date),0,'b'));
My first question is how should I modify this code to create winid that takes into account every quarter rather than just the number of days in a month i.e
PERMNO | DATE | WINID | TIME |
1 | 1-Jan-00 | 1 | Mar-00 |
… | … | 1 | Mar-00 |
1 | 31-Mar-00 | 1 | Mar-00 |
1 | 1-Apr-00 | 2 | Jun-00 |
… | …. | 2 | Jun-00 |
1 | 30-Jun-00 | 2 | Jun-00 |
2 | 1-Jan-00 | 3 | Mar-00 |
… | … | 3 | Mar-00 |
2 | 31-Jan-00 | 3 | Mar-00 |
2 | 1-Feb-00 | 4 | Jun-00 |
… | …. | 4 | Jun-00 |
2 | 28-Feb-00 | 4 | Jun-00 |
My second question is how can I create the fourth variable TIME such that it shows me just the last date of every window as suggested by my table above.
Thanks
Do you understand the algorithm and functions you're already using?
winid + (intnx('month',date,0.'b') ne intnx('month',lag(date),0,'b'));
If you do then it shouldn't be hard to understand that all you need to do is to change the interval from month to quarter.
As for this "time" variable: Function INTNX() allows you to shift dates to the beginning or end of a date/time/datetime interval like quarter.
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.