topic Re: sum in sas in SAS Programming

sum in sas

aditya1 — Fri, 17 Mar 2017 07:32:33 GMT

data flowersales;
input customerid $ saledate mmddyy10. variety $ quantity;
datalines ;
240W 02-07-2003 Ginger 120
240W 02-07-2003 Protea 180
356W 02-08-2003 Heliconia 60
356W 02-08-2003 Anthurium 300
188R 02-11-2003 Ginger 24
188R 02-11-2003 Anthurium 24
240W 02-12-2003 Heliconia 48
240W 02-12-2003 Protea 48
240W 02-13-2003 Ginger 188
188R 02-12-2003 Ginger 500
356W 02-12-2003 Ginger 240
;
run;

Create a dataset "sumsales" (sort it by customer, saledate variety quantity) - keep all original obsservations and variables

this is the program

Create Variable ABCD - show the total sales for each customer

Re: sum in sas

Kurt_Bremser — Fri, 17 Mar 2017 07:55:10 GMT

What have you tried so far?

Re: sum in sas

PeterClemmensen — Fri, 17 Mar 2017 08:31:34 GMT

like this?

data flowersales;
input customerid $ saledate mmddyy10. variety $ quantity;
datalines ;
240W 02-07-2003 Ginger 120
240W 02-07-2003 Protea 180
356W 02-08-2003 Heliconia 60
356W 02-08-2003 Anthurium 300
188R 02-11-2003 Ginger 24
188R 02-11-2003 Anthurium 24
240W 02-12-2003 Heliconia 48
240W 02-12-2003 Protea 48
240W 02-13-2003 Ginger 188
188R 02-12-2003 Ginger 500
356W 02-12-2003 Ginger 240
;

proc sort data = flowersales;
   by customerid saledate variety quantity;
run;

proc sql;
   create table sumsales as 
   select *
         ,sum(quantity) as ABCD
   from flowersales
   group by customerid
   order by customerid, saledate, variety, quantity;
quit;

Re: sum in sas

RW9 — Fri, 17 Mar 2017 09:29:49 GMT

Sounds like a homework question to get you thinking about how to logically approach a problem and then code it...

Re: sum in sas

Astounding — Fri, 17 Mar 2017 09:34:26 GMT

For extra credit: note that the original program is faulty. This statement should appear before the INPUT statement:

length variety $ 9;

Otherwise you will lose anything longer than 8 characters.

Re: sum in sas

Yavuz — Fri, 17 Mar 2017 09:40:04 GMT

i added length clasue as Astounding said, and formatted date.

generally your code looks working.

data flowersales;
length variety $ 9;

INPUT customerid $ saledate mmddyy10. variety $ quantity;
FORMAT saledate mmddyy10.;
DATALINES ;
240W 02-07-2003 Ginger 120
240W 02-07-2003 Protea 180
356W 02-08-2003 Heliconia 60
356W 02-08-2003 Anthurium 300
188R 02-11-2003 Ginger 24
188R 02-11-2003 Anthurium 24
240W 02-12-2003 Heliconia 48
240W 02-12-2003 Protea 48
240W 02-13-2003 Ginger 188
188R 02-12-2003 Ginger 500
356W 02-12-2003 Ginger 240
;
run;

proc sort data = flowersales;
by customerid saledate variety quantity;
run;

proc sql;
create table sumsales as
select *
,sum(quantity) as ABCD
from flowersales
group by customerid
order by customerid, saledate, variety, quantity;
QUIT;

Re: sum in sas

Kurt_Bremser — Fri, 17 Mar 2017 09:43:21 GMT

The proc sort is not necessary, proc sql does that on it's own.

And if the data becomes too large for SQL to handle properly, I'd use a three-step approach with sorting, summarizing into a summary dataset, and merging that back in a data step.

Re: sum in sas

Reeza — Fri, 17 Mar 2017 10:23:03 GMT

@aditya1 wrote:

data flowersales;
input customerid $ saledate mmddyy10. variety $ quantity;
datalines ;
240W 02-07-2003 Ginger 120
240W 02-07-2003 Protea 180
356W 02-08-2003 Heliconia 60
356W 02-08-2003 Anthurium 300
188R 02-11-2003 Ginger 24
188R 02-11-2003 Anthurium 24
240W 02-12-2003 Heliconia 48
240W 02-12-2003 Protea 48
240W 02-13-2003 Ginger 188
188R 02-12-2003 Ginger 500
356W 02-12-2003 Ginger 240
;
run;

Create a dataset "sumsales" (sort it by customer, saledate variety quantity) - keep all original obsservations and variables

this is the program

Create Variable ABCD - show the total sales for each customer