topic Obtaining Distinct Count by groups in SAS Programming

Obtaining Distinct Count by groups

Krueger1 — Thu, 17 Jun 2021 19:37:30 GMT

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;

Re: Obtaining Distinct Count by groups

Kurt_Bremser — Thu, 17 Jun 2021 19:56:27 GMT

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.

Re: Obtaining Distinct Count by groups

Krueger1 — Thu, 17 Jun 2021 20:33:28 GMT

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!

Re: Obtaining Distinct Count by groups

Krueger1 — Fri, 18 Jun 2021 18:24:59 GMT

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;

Re: Obtaining Distinct Count by groups

FreelanceReinh — Tue, 22 Jun 2021 16:42:43 GMT

[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/749624]