Hello. I am brand new to SAS and attempting to create a simplified version of my final product at the moment. My simplified dataset contains 4 columns: The OrderID, the ProductID, the Quantity of each product ordered within the selected order, and the Cost of the product. The OrderID is NOT unique and therefore each order is made up of all the rows containing the same OrderID. I need to find a way to count the number of items in each order (Sum of the quantities over each OrderID) and find the total cost of that order.
I have tried endless amounts of code over the last day and everything I use gives me errors in my log. Any help would be greatly appreciated!
something like this?
data have;
input OrderID Product Quantity TotalValue;
cards;
10248 11 12 5
10248 42 10 2
10248 72 5 4
10249 14 9 9
10249 51 40 1
10250 41 10 1
10250 51 35 6
10250 65 15 5
10251 22 6 5
10251 57 15 2
10251 65 20 4
10252 20 40 9
10252 33 25 1
10252 60 40 1
10253 31 20 6
10253 39 42 5
10253 49 40 5
10254 24 15 2
10254 55 21 4
;
proc sql;
create table want as
select OrderID, sum(Quantity)as count, sum(TotalValue) as totcost
from have
group by orderid;
quit;
something like this?
data have;
input OrderID Product Quantity TotalValue;
cards;
10248 11 12 5
10248 42 10 2
10248 72 5 4
10249 14 9 9
10249 51 40 1
10250 41 10 1
10250 51 35 6
10250 65 15 5
10251 22 6 5
10251 57 15 2
10251 65 20 4
10252 20 40 9
10252 33 25 1
10252 60 40 1
10253 31 20 6
10253 39 42 5
10253 49 40 5
10254 24 15 2
10254 55 21 4
;
proc sql;
create table want as
select OrderID, sum(Quantity)as count, sum(TotalValue) as totcost
from have
group by orderid;
quit;
Thank you so much!!!
Although SQL is good, I recommend using PROC MEANS for summaries to get more information.
Proc means data=have stackods N SUM NWAY;
Class orderID;
VAR <list of variables here>;
ODs output summary = want;
Run;
You can get N, NMISS(), SUM() and MEDIAN, PERCENTILES and other stats from PROC MEANS.
I see this has been answered, but thought I'd give you a couple of other options. I looked through some similar questions posted in the Community and found this post that has three separate solutions. Using your data, I imported and created a dataset work.products. Here is a set of possible solutions.
ods html file='c:\temp\mytables.html';
proc means data=products sum;
class OrderID;
var TotalValue;
run;
proc tabulate data=products f=6. out=work.tabout;
class OrderID;
var TotalValue;
tables OrderID, TotalValue*(N sum);
run;
proc report data=products nowd out=work.repout;
column OrderID TotalValue TotalValue=paysum;
define OrderID / group style(column)=Header;
define TotalValue / n 'Count Indicator';
define paysum / sum 'Order Total';
run;
ods html close;
Join us for SAS Community Trivia
SAS Bowl XLII, SAS Hackathon 2024
Wednesday, August 14, 2024, at 10 a.m. ET | #SASBowl
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.