topic Re: How to add "Total" to the table in New SAS User

How to add "Total" to the table

NazaninSAS — Tue, 04 Dec 2018 15:03:34 GMT

Hi,

I want to generate this table:

REGION	SI_PRI	PRI	Return rate
ATLANTIC	3570	3954	90.3%
HQ	10991	12165	90.3%
ONTARIO	10301	11830	87.1%
PACIFIC	4232	4768	88.8%
PRAIRIES	5279	6297	83.8%
QUEBEC	4399	4950	88.9%
Total	38772	43964	88.9%

this is the code I wrote:

proc sql;

select REGION, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Final2

group by REGION;

quit;

this is the output I receive.

REGION	SI_PRI	PRI	Return rate
ATLANTIC	3570	3954	90.3%
HQ	10991	12165	90.3%
ONTARIO	10301	11830	87.1%
PACIFIC	4232	4768	88.8%
PRAIRIES	5279	6297	83.8%
QUEBEC	4399	4950	88.9%

how can I change my code to get the first table as an output?

Thanks,

Nazanin

Re: How to add "Total" to the table

LinusH — Tue, 04 Dec 2018 15:17:00 GMT

Usually you don't use SQL to create end user reports.

take a look at PROC TABULATE and PROC REPORT.

Re: How to add "Total" to the table

PeterClemmensen — Tue, 04 Dec 2018 15:17:09 GMT

PROC SQL is probably not the right tool here. I take it that you want a report and not a data set, correct? See PROC REPORT Documentation for examples of this kind of issue.

Re: How to add "Total" to the table

RW9 — Tue, 04 Dec 2018 15:26:16 GMT

This comes up one every 20 posts or so. Please use the search functionality to look before posting. Here is one from yesterday:

https://communities.sas.com/t5/SAS-Programming/insert-a-row-at-the-end-of-specific-rows-with-Totals/m-p/518000#M140142

Re: How to add "Total" to the table

Jagadishkatam — Tue, 04 Dec 2018 15:29:28 GMT

for creating the dataset in proc sql try the below code

data have;
input REGION $	SI_PRI	PRI	Return_rate;
cards;
ATLANTIC 3570 3954 0.903
HQ 10991 12165 0.903
ONTARIO 10301 11830 0.871
PACIFIC 4232 4768 0.888
PRAIRIES 5279 6297 0.838
QUEBEC 4399 4950 0.889
;

proc sql;
create table want as 
 select 'Total' as region , sum(SI_PRI) as SI_PRI, sum(PRI) as PRI, sum(Return_rate) from have 
 union 
 select * from have;
quit;

Re: How to add "Total" to the table

Ksharp — Wed, 05 Dec 2018 13:43:19 GMT

Code not tested.

proc sql;

select REGION, count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Final2

group by REGION

union all

select 'TOTAL', count(SI_PRI) as SI_PRI, count (PRI)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Final2

;

quit;

Re: How to add "Total" to the table

NazaninSAS — Wed, 05 Dec 2018 13:52:59 GMT

That is just perfect.

It works very well.

Thanks a lot,

Nazanin

Re: How to add "Total" to the table

PaigeMiller — Wed, 05 Dec 2018 14:04:50 GMT

I don't think the code from @Ksharp gives correct answers in the presence of missing values for PRI or SI_PRI.

Re: How to add "Total" to the table

NazaninSAS — Wed, 05 Dec 2018 14:07:01 GMT

Thanks,

I have missing values in SI-PRI.

why do you think it will not give a correct answer?

Nazanin

Re: How to add "Total" to the table

PaigeMiller — Wed, 05 Dec 2018 14:07:52 GMT

Because the count function treats them the same as if the values were not missing.

Re: How to add "Total" to the table

Ksharp — Wed, 05 Dec 2018 14:10:34 GMT

Ou. I know what Paige means.

If you want include missing value into TOTAL row.

Try:

union all

select 'TOTAL', count(*) as SI_PRI, count (*)as PRI,

calculated SI_PRI/calculated PRI "Return rate" format=percent8.1

from work.Final2

;

Re: How to add "Total" to the table

NazaninSAS — Wed, 05 Dec 2018 14:13:59 GMT

the first one was correct.

I want the sum of the above rows!

the second one gives me the total of all rows!

Thanks,

Nazanin

Re: How to add "Total" to the table

PaigeMiller — Wed, 05 Dec 2018 14:17:36 GMT

Well, not so fast.

The problem you will have is if either PRI or SI_PRI is missing and the other one is not missing. The end result, the ratio, still will not be correct if this happens.

Re: How to add "Total" to the table

NazaninSAS — Wed, 05 Dec 2018 14:22:06 GMT

this is what I get:

REGION	SI_PRI	PRI	Return rate
ATLANTIC	3570	3954	90.3%
HQ	10991	12165	90.3%
ONTARIO	10301	11830	87.1%
PACIFIC	4232	4768	88.8%
PRAIRIES	5279	6297	83.8%
QUEBEC	4399	4950	88.9%
TOTAL	38772	43964	88.2%

it seems correct 🙂

I always have missing in SI_PRI because of that I calculate the return rate to see how many responded.

Re: How to add "Total" to the table

PaigeMiller — Wed, 05 Dec 2018 14:27:38 GMT

Ok, that's great it works for your data.

But for any other data sets and for anyone else reading along, in general, this process of performing statistics in PROC SQL on two different variables and then taking the ratio is not going to work if one variable is missing and the other variable is present. I see this done often to compute weighted averages in SQL, and as stated, missing values are not handled properly and the wrong value results. This is why I always recommend PROC SUMMARY to do weighted averages and the like, because the missings are handled properly. PROC SUMMARY also has the benefit of automatically computing totals for you, and you don't have to write and debug extra SQL code just to get the totals.

Re: How to add "Total" to the table

NazaninSAS — Wed, 05 Dec 2018 14:30:00 GMT

I'm also going to try ProcSummary.

would you please guide me to a good source for PROCSummary?

Thanks,

Nazanin

Re: How to add "Total" to the table

PaigeMiller — Wed, 05 Dec 2018 14:44:24 GMT

The only source I know of is the SAS documentation at:

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=proc&docsetTarget=p0aq3hsvflztfzn1xa2wt6s35oy6.htm&locale=en

There are examples in the PROC MEANS documentation which should work identically in PROC SUMMARY (but I haven't tested them in PROC SUMMARY)

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=proc&docsetTarget=p0f0fjpjeuco4gn1ri963f683mi4.htm&locale=en

The only quirk you might want to know is that PROC SUMMARY puts the totals at the top of the output data set, whereas you want them at the bottom, but you can do a sort BY DESCENDING _TYPE_; and then the totals are at the bottom.