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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.