I am struggling with creating an enumeration variable for the following data
(I have several accounts that look like this)
Acctnum | date1 | date2 |
001 | 1/1/2010 | . |
001 | 2/1/2010 | . |
001 | 3/1/2010 | . |
001 | 4/1/2010 | . |
001 | 5/1/2010 | . |
001 | 6/1/2010 | 6/1/2010 |
001 | 7/1/2010 | . |
001 | 8/1/2010 | . |
001 | 9/1/2010 | . |
001 | 10/1/2010 | 10/1/2010 |
001 | 11/1/2010 | . |
001 | 12/1/2010 | . |
001 | 1/1/2011 | . |
001 | 2/1/2011 | . |
001 | 3/1/2011 | . |
001 | 4/1/2011 | 4/1/2011 |
And I want my output to create a count of period like this
Acctnum | date1 | date2 | count |
001 | 1/1/2010 | . | . |
001 | 2/1/2010 | . | . |
001 | 3/1/2010 | . | . |
001 | 4/1/2010 | . | . |
001 | 5/1/2010 | . | . |
001 | 6/1/2010 | 6/1/2010 | 0 |
001 | 7/1/2010 | . | 1 |
001 | 8/1/2010 | . | 2 |
001 | 9/1/2010 | . | 3 |
001 | 10/1/2010 | 10/1/2010 | 0 |
001 | 11/1/2010 | . | 1 |
001 | 12/1/2010 | . | 2 |
001 | 1/1/2011 | . | 3 |
001 | 2/1/2011 | . | 4 |
001 | 3/1/2011 | . | 5 |
001 | 4/1/2011 | 4/1/2011 | 0 |
Any help will be truly appreciated!!
data want; set have;
by id; retain count; if first.id then count=.;
if date2 ne . then count=0; else if count ne . then count=count+1; run;
I can't test it though.
Well, your data isn't easy to run is SAS so I haven't tested this, but something like:
data want; set have; retain count; if date2 ne . then count=0; else if count ne . then count=count+1; run;
Thanks RW9 for the quick reply.
The code works, but the enumeration continues into other account numbers.
Is there a way to restart the process for each account?
Add a BY variable and reset at the first occurrence.
BY Account;
if first.account then do;
*reset variables that need it;
*end;
As @Reeza has said, add a by group then.
data want; set have;
by id; retain count; if first.id or date2 ne . then count=0; else if count ne . then count=count+1; run;
note it has to be sorted by ID.
data want; set have;
by id; retain count; if first.id then count=.;
if date2 ne . then count=0; else if count ne . then count=count+1; run;
I can't test it though.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.