Help using Base SAS procedures

Enumeration with a condition within by group

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Enumeration with a condition within by group

[ Edited ]

I am struggling with creating an enumeration variable for the following data

(I have several accounts that look like this)

 

Acctnumdate1date2
0011/1/2010.
0012/1/2010.
0013/1/2010.
0014/1/2010.
0015/1/2010.
0016/1/20106/1/2010
0017/1/2010.
0018/1/2010.
0019/1/2010.
00110/1/201010/1/2010
00111/1/2010.
00112/1/2010.
0011/1/2011.
0012/1/2011.
0013/1/2011.
0014/1/20114/1/2011

 

And I want my output to create a count of period like this

Acctnumdate1date2count
0011/1/2010..
0012/1/2010..
0013/1/2010..
0014/1/2010..
0015/1/2010..
0016/1/20106/1/20100
0017/1/2010.1
0018/1/2010.2
0019/1/2010.3
00110/1/201010/1/20100
00111/1/2010.1
00112/1/2010.2
0011/1/2011.3
0012/1/2011.4
0013/1/2011.5
0014/1/20114/1/20110

 

Any help will be truly appreciated!!


Accepted Solutions
Solution
‎03-17-2016 11:21 AM
Super User
Super User
Posts: 7,430

Re: Enumeration with a condition within by group

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. 

View solution in original post


All Replies
Super User
Super User
Posts: 7,430

Re: Enumeration with a condition within by group

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;
New Contributor
Posts: 4

Re: Enumeration with a condition within by group

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?

 

Super User
Posts: 17,949

Re: Enumeration with a condition within by group

Add a BY variable and reset at the first occurrence.

 

BY Account;

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

*end;
Super User
Super User
Posts: 7,430

Re: Enumeration with a condition within by group

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. 

New Contributor
Posts: 4

Re: Enumeration with a condition within by group

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.
Solution
‎03-17-2016 11:21 AM
Super User
Super User
Posts: 7,430

Re: Enumeration with a condition within by group

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. 

New Contributor
Posts: 4

Re: Enumeration with a condition within by group

Thanks @RW9.
The solution works - truly appreciate your help!!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 453 views
  • 4 likes
  • 3 in conversation