Fluorite | Level 6

## Obtaining Distinct Count by groups

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
Super User

## Re: Obtaining Distinct Count by groups

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.

4 REPLIES 4
Super User

## Re: Obtaining Distinct Count by groups

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.

Fluorite | Level 6

## Re: Obtaining Distinct Count by groups

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!

Fluorite | Level 6

## Re: Obtaining Distinct Count by groups

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;```