Solved
Contributor
Posts: 53

Add an observation with Total

Below is my current code and its output

Customer_name     Model_service_type     YTD_ACO

A                              1                                   \$5

A                              2                                   \$3

A                              3                                   .

A                              4                                   \$1

B                              1                                   \$10

B                              2                                   \$2

B                              3                                   \$4

proc sql;

create table tmp_model as

select customer_name, model_service_type, sum(cost) as YTD_ACO format=dollar16.

from dfrout.current_mod

where year=year(date())

group by customer_name, calculated model_service_type_name;

quit;

I would like to modify this code to output an additional observation that totals by customer.

Customer_name     Model_service_type     YTD_ACO

A                              1                                   \$5

A                              2                                   \$3

A                              3                                   .

A                              4                                   \$1

A                              total                               \$9

B                              1                                   \$10

B                              2                                   \$2

B                              3                                   \$4

B                              total                               \$16

I thought this was covered in procSQL1 class but just have not been able to find it in my book.  Any suggestions would be greatly appreciated.

Thanks

Accepted Solutions
Solution
‎05-07-2014 07:56 PM
Super Contributor
Posts: 644

Re: Add an observation with Total

Refinement on Reeza's solution

Use descending to get sum after data

Assuming data sorted by customer_name

proc means data=dfrout.current_mod noprint descending ;

By customer_name ;

class model_service_type ;

id <any other customer level information you want to keep > ;

output out=want (Drop = _TYPE_ _Freq_)

sum(YTD_ACO) =;

format YTD_ACO dollar16.

model_service_type Showtotal. ;

run;

Where format Showtotal (or \$Showtotal if model_service_type is character) provides the label "Total" when model_service_type is null

Richard

All Replies
Super User
Posts: 23,724

Re: Add an observation with Total

You're not summarizing any variables (ie sum/count/mean) in the SQL step so what does the Group by clause accomplish for you?

Do you need the data set to have those values in OR do you need to output those totals out?

Contributor
Posts: 53

Re: Add an observation with Total

Sorry i tried to simplify the code for this message and accidentally deleted my sum.   Added that back in now.  I need the output dataset to have the totals.

Super User
Posts: 23,724

Re: Add an observation with Total

Consider using proc means instead.

proc means data=dfrout.current_mod noprint;

class customer_name model_service_type;

output out=want sum(cost) = YTD_ACO;

format cost dollar16.;

run;

Take a look at your output data set, want. You may need to sort it and or format it.

Solution
‎05-07-2014 07:56 PM
Super Contributor
Posts: 644

Re: Add an observation with Total

Refinement on Reeza's solution

Use descending to get sum after data

Assuming data sorted by customer_name

proc means data=dfrout.current_mod noprint descending ;

By customer_name ;

class model_service_type ;

id <any other customer level information you want to keep > ;

output out=want (Drop = _TYPE_ _Freq_)

sum(YTD_ACO) =;

format YTD_ACO dollar16.

model_service_type Showtotal. ;

run;

Where format Showtotal (or \$Showtotal if model_service_type is character) provides the label "Total" when model_service_type is null

Richard

Occasional Contributor
Posts: 12

Re: Add an observation with Total

proc sql;

create table want as

select * from have

outer union corr

select * from (select distinct Customer_name,'total' as Model_service_type,sum(YTD_ACO) as YTD_ACO

from have group by Customer_name)

order by Customer_name,Model_service_type;

quit;

Contributor
Posts: 35

Re: Add an observation with Total

Hi .. here's something with Data step..

Data have;

input Customer_name \$     Model_service_type \$    YTD_ACO :dollar2.;

datalines;

A                              1                                   \$5

A                              2                                   \$3

B                              3                                   .

B                              4                                   \$1

C                              1                                   \$10

C                              2                                   \$2

C                              3                                   \$4

;

run;

Proc sort data=have;

by Customer_name;

run;

Data WANT;

retain sum 0;

set test1;

by Customer_name;

if first.Customer_name=1 then

sum=0;

sum+YTD_ACO;

output;

if last.Customer_name=1 then

do;

Model_service_type="TOTAL";

YTD_ACO=sum;

output;

end;

drop sum;

format YTD_ACO dollar8.;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 6 replies
• 314 views
• 3 likes
• 5 in conversation