Count distinct function in proc sql

[greg6363]

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. 

[mkeintz]

---

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

[greg6363]

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.

[Kurt_Bremser · Accepted solution]

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

[greg6363]

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