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;
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.
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.
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!
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;
[A new thread has been started to discuss this follow-up question: https://communities.sas.com/t5/SAS-Programming/Increment-on-out-of-order-but-distinct-values/m-p/749...]
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.