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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1960 views
  • 2 likes
  • 7 in conversation