BookmarkSubscribeRSS Feed
aditya1
Calcite | Level 5

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 

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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.

Yavuz
Quartz | Level 8

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;

 

Kurt_Bremser
Super User

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.

Reeza
Super User

@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 


 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1927 views
  • 2 likes
  • 7 in conversation