Basic Sum and Count Question

[FeedMePi]

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! 

[novinosrin · Accepted solution]

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;

[FeedMePi]

Thank you so much!!! 

[Reeza]

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. 

[joeFurbee]

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; 

[FeedMePi]

That works wonderfully too! Thank you so much! I’ve done programming in the past, but SAS is a completely foreign concept and my brain just isn’t quite grasping it yet.