topic Re: Count of IDs by a combination of fields in SAS Programming

Count of IDs by a combination of fields

altijani — Fri, 29 Jan 2021 15:24:05 GMT

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.

Re: Count of IDs by a combination of fields

Angel_Larrion — Fri, 29 Jan 2021 17:07:12 GMT

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;

Re: Count of IDs by a combination of fields

Angel_Larrion — Fri, 29 Jan 2021 17:12:27 GMT

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

Re: Count of IDs by a combination of fields

altijani — Fri, 29 Jan 2021 19:36:51 GMT

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?

Re: Count of IDs by a combination of fields

Angel_Larrion — Fri, 29 Jan 2021 19:47:14 GMT

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)?

Re: Count of IDs by a combination of fields

altijani — Fri, 29 Jan 2021 19:49:17 GMT

Yes. Many.

Re: Count of IDs by a combination of fields

Angel_Larrion — Fri, 29 Jan 2021 20:00:56 GMT

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;