DATA Step, Macro, Functions and more

sum in sas

Reply
New Contributor
Posts: 3

sum in sas

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 

Super User
Posts: 7,760

Re: sum in sas

What have you tried so far?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 733

Re: sum in sas

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;
Super User
Super User
Posts: 7,942

Re: sum in sas

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

Super User
Posts: 5,497

Re: sum in sas

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.

Contributor
Posts: 54

Re: sum in sas

Posted in reply to Astounding

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;

 

Super User
Posts: 7,760

Re: sum in sas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,770

Re: sum in sas


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 


 

Ask a Question
Discussion stats
  • 7 replies
  • 168 views
  • 2 likes
  • 7 in conversation