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.
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 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?
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.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: