topic Re: incrementing a variable when another variable changes value in SAS Programming

incrementing a variable when another variable changes value

AllSoEasy — Fri, 27 Sep 2013 15:13:05 GMT

Hello all,

I have a fairly general question -- how to create a variable that increments whenever another variable changes value; this would be useful for me in many situations, but one specific example I have right now, is if I have a set of data, ordered by "as-of-date" as such:

AS_OF_DATE
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-01
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-02
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-05
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-06
2013-08-07
2013-08-07
2013-08-07
2013-08-07
2013-08-07
2013-08-07
2013-08-07

etc...

And I want to create a variable "dayNum" which starts with a value of 1 on the first date (in this case 2013-08-01) and then retains the value of 1 for all rows with this date, and then gets incremented to a value of 2 when the date changes (in this case the next date would be 2013-08-02) and then retains the value of 2 for all rows with this date, then increments and retains a value of 3 for all rows of the next date (2013-08-05) etc. -- The dates don't necessarily increment one by one, as we can see the data jumps from 08-02 to 08-05 and the full dataset spans accross mutliple months so I can't just assume the day number or something that simple. Still this seems like it should be a fairly simple task but I can't think of any simplistic/efficient way of doing this. Any suggestions would be appreciated tremendously.

Thanks!!

Re: incrementing a variable when another variable changes value

Reeza — Fri, 27 Sep 2013 15:14:37 GMT

SAS FAQ: How can I create an enumeration variable by groups?

Re: incrementing a variable when another variable changes value

AllSoEasy — Fri, 27 Sep 2013 15:27:13 GMT

EDIT - Nevermind, I made a silly mistake, and this article did provide the information needed. Thank you Reeza!!

Re: incrementing a variable when another variable changes value

Reeza — Fri, 27 Sep 2013 15:32:41 GMT

You have no BY?

And daynum=1 always resets it to 1, you need to set it to 0 or 1 for the first record only.

Daynum+1 allows for an implicit retain statement, so the first option doesn't use retain, but sets it to 1 using the automatic counter _n_.

The second option uses retain to set it to 0 initially.

data newdata;

set mydatra;

BY as_of_date;

if _n_=1 then dayNum=0;

if first.as_of_date then dayNum+1;

run;

data newdata;

set mydatra;

BY as_of_date;

retain daynum 0;

if first.as_of_date then dayNum+1;

run;

Re: incrementing a variable when another variable changes value

data_null__ — Fri, 27 Sep 2013 16:19:09 GMT

Somewhat awkward looking examples. I believe example 2 demonstrates the author does not have a good grasp of first. and last.

data two1;

set two;

count + 1;

by class gender;

if first.class or first.gender then count = 1;

run;

Reeza wrote:

SAS FAQ: How can I create an enumeration variable by groups?

Re: incrementing a variable when another variable changes value

Reeza — Fri, 27 Sep 2013 16:43:44 GMT

I agree, some of the stuff on there is outdated as well.