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!!
EDIT - Nevermind, I made a silly mistake, and this article did provide the information needed. Thank you Reeza!!
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;
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?
I agree, some of the stuff on there is outdated as well.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.