creating a variable

[Amalik]

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.

[ChrisNZ]

I am unsure I fully understand. Try:

  WINID + ( dif(DATE) ne 0 | dif(PERMO) ne 0 );

[Tom · Accepted solution]

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'));

[Amalik]

Thanks Tom, this solves my issue.

[Amalik]

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