DATA Step, Macro, Functions and more

incrementing a variable when another variable changes value

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 105
Accepted Solution

incrementing a variable when another variable changes value

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


Accepted Solutions
Solution
‎09-27-2013 11:14 AM
Super User
Posts: 17,758

Re: incrementing a variable when another variable changes value


All Replies
Solution
‎09-27-2013 11:14 AM
Super User
Posts: 17,758

Re: incrementing a variable when another variable changes value

Frequent Contributor
Posts: 105

Re: incrementing a variable when another variable changes value

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

Super User
Posts: 17,758

Re: incrementing a variable when another variable changes value

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;

OR

data newdata;

set mydatra;

BY as_of_date;

retain daynum 0;

if first.as_of_date then dayNum+1;

run;

Respected Advisor
Posts: 3,777

Re: incrementing a variable when another variable changes value

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?

Super User
Posts: 17,758

Re: incrementing a variable when another variable changes value

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

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 1631 views
  • 2 likes
  • 3 in conversation