topic Count distinct function in proc sql in SAS Programming

Count distinct function in proc sql

greg6363 — Thu, 19 Nov 2020 06:15:29 GMT

I am attempting to perform a count of deliveries for each AcctNo.

Here is the data set:

Brand AcctNo DeliveryDate DeliveryUnits

A 21120 7/31/20 100

A 21120 8/7/20 150

A 21120 8/14/20 200

A 21120 8/21/20 100

B 21121 8/5/20 350

B 21121 8/12/20 250

B 21121 8/19/20 300

proc sql;
create table master as
select brand, count(distinct AcctNo) as accts format comma14.,
count(delivery_date) as drops format comma14., sum(DeliveryUnits) as drops_units format comma14.2
from minor
group by brand;
quit;

In this summary query, I want to count 4 deliveries for one AcctNo when grouping by brand which is why I used to distinct command to count each AcctNo only once. Unfortunately, the output gives me the exact same number for both counts.

Any feedback would be greatly appreciated. Thanks.

Re: Count distinct function in proc sql

mkeintz — Thu, 19 Nov 2020 06:37:19 GMT

@greg6363 wrote:

I am attempting to perform a count of deliveries for each AcctNo.

Here is the data set:

Brand AcctNo DeliveryDate DeliveryUnits

   A    21120    7/31/20    100

   A    21120    8/7/20    150

   A    21120    8/14/20    200

   A    21120    8/21/20    100

   B    21121    8/5/20    350

   B    21121    8/12/20    250

   B    21121    8/19/20    300

proc sql;
create table master as
select brand, count(distinct AcctNo) as accts format comma14.,
count(delivery_date) as drops format comma14., sum(DeliveryUnits) as drops_units format comma14.2
from minor
group by brand;
quit;

In this summary query, I want to count 4 deliveries for one AcctNo when grouping by brand which is why I used to distinct command to count each AcctNo only once. Unfortunately, the output gives me the exact same number for both counts.

Any feedback would be greatly appreciated. Thanks.

I don't understand what you think is wrong - what values did you get, and what values did you expect?

Re: Count distinct function in proc sql

greg6363 — Thu, 19 Nov 2020 06:52:46 GMT

This is the output I expect in the summary output:

Brand Accts Drops DropsUnits

A 1 4 550

B 1 3 900

This is the output I get from my summary output:

Brand Accts Drops DropsUnits

A 4 4 550

B 3 3 900

The Accts and Drops fields should not produce the same value when using the distinct option.

Re: Count distinct function in proc sql

Kurt_Bremser — Thu, 19 Nov 2020 07:02:10 GMT

Then your data is not as posted, or you ran a different code:

data minor;
input Brand $ AcctNo $ DeliveryDate :mmddyy10. DeliveryUnits;
format deliverydate yymmdd10.;
datalines;
   A        21120      7/31/20            100
   A        21120      8/7/20              150
   A        21120      8/14/20            200
   A        21120      8/21/20            100
   B        21121      8/5/20              350 
   B        21121      8/12/20            250
   B        21121      8/19/20            300
;

proc sql;
create table master as
  select
    brand,
    count(distinct AcctNo) as accts format comma14.,
    count(deliverydate) as drops format comma14.,
    sum(DeliveryUnits) as drops_units format comma14.2
  from minor
  group by brand
;
quit;

proc print noobs;
run;

Result:

Brand	accts	drops	drops_units
A	1	4	550.00
B	1	3	900.00

Re: Count distinct function in proc sql

greg6363 — Thu, 19 Nov 2020 16:19:54 GMT

Your code works but mine doesn't. Strange. Let me go back and review my data formats. Thanks.