BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daretowin
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
daretowin
Fluorite | Level 6

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?

 

Reeza
Super User

Add a BY variable and reset at the first occurrence.

 

BY Account;

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

*end;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

daretowin
Fluorite | Level 6
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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. 

daretowin
Fluorite | Level 6
Thanks @RW9.
The solution works - truly appreciate your help!!

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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