topic Re: Count Id once in a group in SAS Programming

Count Id once in a group

sascode — Wed, 05 Oct 2022 02:42:05 GMT

Hello,

I have a dataset as below;
data a;
input ID $ Item_id $5.;
datalines;
a1 ABC10
a2 ABC11
a2 ABC12
a2 ABC13
b1 ABC11
b2 ABC11
c1 ABC10
;
run;

Uisng the link below , i can count distinct ID's for each item using below code:
proc sql;
select item_id,
count(distinct ID) as Distinct_Id from a
group by Item_id;
quit;
After seeing the output i am noticing in last two rows that

ABC12 and ABC13 have 1 disctinct id.

However, this ID is same (a2) , therefore , if it has been included for first item count than it should not be

counted in the second item group.

I would appreciate any suggestion.

Re: Count Id once in a group

ballardw — Wed, 05 Oct 2022 04:47:01 GMT

So should Item_id ABC12 and ABC13 even be in the output since they do not have "a distinct" id? Or should the Id be blank/missing?

Re: Count Id once in a group

Patrick — Wed, 05 Oct 2022 04:55:06 GMT

a2 already exists for item_id ABC11.

So what exactly is your rule for counting distinct IDs? Only count for their first appearance in any group? And if so what's the sort order for the groups (or you don't care)?

Re: Count Id once in a group

sascode — Wed, 05 Oct 2022 11:33:06 GMT

yes, it is fine if it show 0 .
Thank you.

Re: Count Id once in a group

sascode — Wed, 05 Oct 2022 11:34:34 GMT

Hi,
Yes, if an id is counted once in a given group , it should not counted again in the next group .
Sorting is not a problem

Re: Count Id once in a group

ballardw — Wed, 05 Oct 2022 14:27:24 GMT

@sascode wrote:
yes, it is fine if it show 0 .
Thank you.

Then maybe something like:

proc sort data=a;
  by id;
run;

data need;
   set a;
   by id;
   if first.id then do;
   end;
   else call missing(id);
run;

proc sql;
select item_id,
count(distinct ID) as Distinct_Id from need
group by Item_id;
quit;

The sort groups all of the ID values together and then the data step sets all but one to missing. That way there is no "duplicate" Id to count.

Caution: this makes no attempt to control which "item_id" gets the Id. You might sort by Id and Item_id, then the first Item_id by alphabetic value would be associated with the ID. More complex rules of which Item_id should have the Id may be a bit difficult to enforce without a lot of work.