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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.