topic Re: SQL command in SAS Procedures

SQL command

deleted_user — Thu, 26 Jun 2008 11:24:27 GMT

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.

Re: SQL command

Olivier — Tue, 01 Jul 2008 09:04:36 GMT

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

Re: SQL command

deleted_user — Tue, 01 Jul 2008 10:04:53 GMT

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;

Re: SQL command

deleted_user — Wed, 02 Jul 2008 19:14:16 GMT

As oliver said proc is the best way.. U may use an ods to convert is back to data set..