DATA Step, Macro, Functions and more

creating a variable

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

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.

 

PERMNODATEWINID
11-Jan-001
1
131-Jan-001
11-Feb-002
….2
128-Feb-002
21-Jan-003
3
231-Jan-003
21-Feb-004
….4
228-Feb-004

 

Thanks.


Accepted Solutions
Solution
3 weeks ago
Super User
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'));

View solution in original post


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
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 ?

 

PERMNODATEWINIDTIME
11-Jan-001Jan-00
1Jan-00
131-Jan-001Jan-00
11-Feb-002Feb-00
….2Feb-00
128-Feb-002Feb-00
21-Jan-003Jan-00
3Jan-00
231-Jan-003Jan-00
21-Feb-004Feb-00
….4Feb-00
228-Feb-004Feb-00
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 159 views
  • 0 likes
  • 3 in conversation