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