Solved
Contributor
Posts: 52

# creating a variable

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.

Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 7,938

## Re: creating a variable

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

All Replies
PROC Star
Posts: 2,319

## Re: creating a variable

I am unsure I fully understand. Try:

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

Solution
3 weeks ago
Super User
Posts: 7,938

## Re: creating a variable

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'));``
Contributor
Posts: 52

## Re: creating a variable

Thanks Tom, this solves my issue.

Contributor
Posts: 52

## Re: creating a variable

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
☑ This topic is solved.