Help using Base SAS procedures

SQL command

Reply
N/A
Posts: 0

SQL command

I have data
productcode and cost of each product, i group it by productcode i have total cost of each productcode but i don't have the amount of total cost of every product at the last line, could you help me how to do ?
productcode cost
A 100
A 20
B 200
B 50
C 220
....
my command
select productcode,sum(cost)
from data
group by productcode


I need the last line 490 (total of every product)
many thanks.
Super Contributor
Posts: 260

Re: SQL command

Posted in reply to deleted_user
This is not really the SQL way of thinking to add grand total lines in a report or a data set. What you can do is same the result of your query in a SAS data set (CREATE TABLE ... AS SELECT etc.) and then display the dataset with a PRINT procedure, using the SUM statement to add the final row :
[pre]
PROC PRINT DATA = queryResult LABEL NOOBS ;
SUM cost ;
RUN ;
[/pre]
Regards.
Olivier
N/A
Posts: 0

Re: SQL command

Posted in reply to deleted_user
Hi Photo,
For accomplishing your task, the code posted by Mr.Olivier is enough but for creating a data set, the following code is helpful

data total;
set productcode;
by productcode;
if first.productcode then do;
total_cost+cost;
end;
if last.productcode;
run;
N/A
Posts: 0

Re: SQL command

Posted in reply to deleted_user
As oliver said proc is the best way.. U may use an ods to convert is back to data set..
Ask a Question
Discussion stats
  • 3 replies
  • 134 views
  • 0 likes
  • 2 in conversation