Count of IDs by a combination of fields

[altijani]

Hello 

I need to use the below data to count the number of non-missing IDs. The goal is to reach the percentage completion for each region:

Location	Store	Case_ID
East	A	Case1
East	A	Case1
West	B	.
South	X	Case2
South	X	.
South	Y	Case3
South	Y	Case3
South	Z	Case4
West	A	Case5

Requested results:

Location	Number UNIQUE Stores	Non-Missing IDs for these stores	% completion IDs
East	1	1	100%
West	2	1	50%
South	3	3	100%

Thank you everyone.

[Angel_Larrion]

You can obtain that with a proc sql:

proc sql;
create table want as
select Location, 
	   count(distinct Store) as unique_stores,
	   count(distinct Case_ID) as not_missing_id, 
	   count(distinct Case_ID)/count(distinct Store) as cmplt_id format=percent7.
from have
group by Location;
quit;

[Angel_Larrion]

The count function ignores missing values, and the distinct option counts unique values.

[altijani]

Hi. Thanks, but this code does not provide the results I am looking for. It provides instead this results as 1, 1, 1 

I am looking for

1. the total number of stores in each region

2. For those stores, how many of them have completed (non missing) IDs?

[Angel_Larrion]

i ran that code and this is the output table

in the Case_id column do you have dots or missing values (i.e. space blank)?

[altijani]

Yes. Many.

[Angel_Larrion]

could you try this code and share the output:

data have;
set have;
if case_id="." then case_id="";
run;

proc sql;create table want as
select Location, 
	   count(distinct Store) as unique_stores,
	   count(distinct Case_ID) as not_missing_id, 
	   count(distinct Case_ID)/count(distinct Store) as cmplt_id format=percent7.
from have
group by Location;
quit;