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
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;
Sounds like a homework question to get you thinking about how to logically approach a problem and then code it...
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.
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;
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.
@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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.