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

Hello, I'm struggling to do an incremental count by distinct ID's. Below is an example of my expectation.

 

Basically, I want to know how many distinct ID's there are by customer (in order of date) between any results of -4. So after that -4 result we begin counting again. 

 

data have;
input NAME $ DATE ID RESULT;
format DATE yymmdd10.;
cards;
Joe 20190101 1 500
Joe 20190102 1 500
Joe 20190103 1 500
Joe 20190104 1  -4
Tom 20190104 1 500
Tom 20190101 1  -4
Tom 20190102 1 500
Tom 20190103 2 500
Tom 20190104 3 500
Tom 20190104 7  -4
;
run;

data want;
input NAME $ DATE ID RESULT want;
format DATE yymmdd10.;
cards;
Joe 20190101 1 500 1
Joe 20190102 1 500 1
Joe 20190103 1 500 1
Joe 20190104 1  -4 1
Tom 20190104 1 500 1
Tom 20190101 1  -4 1
Tom 20190102 1 500 1
Tom 20190103 2 500 2
Tom 20190104 3 500 3
Tom 20190104 7  -4 4
;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

See if this works:

data want;
set have;
by name id /* notsorted if id is not ascending */;
retain want;
if first.name or lag(result) = -4 then want = 1;
else if first.id then want + 1;
run;

Untested, posted from my tablet.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

See if this works:

data want;
set have;
by name id /* notsorted if id is not ascending */;
retain want;
if first.name or lag(result) = -4 then want = 1;
else if first.id then want + 1;
run;

Untested, posted from my tablet.

Krueger1
Fluorite | Level 6

This appears to work, still need to test in the larger scheme of things. I was missing the else if in my attempts!

 

Thanks so much!

Krueger1
Fluorite | Level 6

Thank you for the response, so this was SUPER close however it's not catching DISTINCT ID's. Below is a better example of this. Currently, if ID 1 appears, then 2 then 1 again it'll increment to 3 when it should only be 2. 

data have;
input NAME $ Flag DATE ID RESULT;
format DATE yymmdd10.;
cards;
Joe 0 20190101 1 500
Joe 0 20190102 1 500
Joe 0 20190103 1 500
Joe 0 20190104 1  -4
Joe 1 20190121 1 500
Joe 0 20190122 1 500
Tom 0 20190104 1 500
Tom 0 20190101 1  -4
Tom 0 20190102 1 500
Tom 0 20190103 2 500
Tom 0 20190104 3 500
Tom 0 20190105 1 500
Tom 0 20190104 7  -4
;
run;

data want;
input NAME $ DATE ID RESULT want;
format DATE yymmdd10.;
cards;
Joe 0 20190101 1 500 1
Joe 0 20190102 1 500 1
Joe 0 20190103 1 500 1
Joe 0 20190104 1  -4 1
Joe 1 20190121 1 500 1
Joe 0 20190122 1 500 1
Tom 0 20190104 1 500 1
Tom 0 20190101 1  -4 1
Tom 0 20190102 1 500 1
Tom 0 20190103 2 500 2
Tom 0 20190104 3 500 3
Tom 0 20190105 1 500 3
Tom 0 20190106 2 500 3
Tom 0 20190104 7  -4 1
;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 620 views
  • 1 like
  • 3 in conversation