topic Re: Enumeration with a condition within by group in SAS Procedures

Enumeration with a condition within by group

daretowin — Thu, 17 Mar 2016 14:10:58 GMT

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

Re: Enumeration with a condition within by group

RW9 — Thu, 17 Mar 2016 14:27:03 GMT

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;

Re: Enumeration with a condition within by group

daretowin — Thu, 17 Mar 2016 14:36:03 GMT

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?

Re: Enumeration with a condition within by group

Reeza — Thu, 17 Mar 2016 14:43:50 GMT

Add a BY variable and reset at the first occurrence.

BY Account;

if first.account then do;
*reset variables that need it;

*end;

Re: Enumeration with a condition within by group

RW9 — Thu, 17 Mar 2016 14:48:45 GMT

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.

Re: Enumeration with a condition within by group

daretowin — Thu, 17 Mar 2016 15:02:50 GMT

Thanks @RW9 !

The code works except that the enumeration starts at each id, and resets at date2 ne .
I would like the enumeration to start at date2 ne . within each id.

Re: Enumeration with a condition within by group

RW9 — Thu, 17 Mar 2016 15:11:08 GMT

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.

Re: Enumeration with a condition within by group

daretowin — Thu, 17 Mar 2016 15:22:57 GMT

Thanks @RW9.
The solution works - truly appreciate your help!!