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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1244 views
  • 1 like
  • 3 in conversation