topic Re: How to put "Total" in a column of stabsummary in SAS Enterprise Guide

How to put "Total" in a column of stabsummary

peter2 — Tue, 29 Nov 2016 10:21:01 GMT

Hello,

I make a request of stabsummary and in the column of "SGE", the value "total" is missing. I have no value and i want t o put "Total".

I try to put the put format you can see in the code but it's KO.

Can you help me please?

Thank you



PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_STABSUMMARYTABLESQUERY(label="QUERY_FOR_STABSUMMARYTABLESQUERY_TESTSGE") AS 
   SELECT t1.SGE, 
          t1.COUNT_of_SGE_Sum FORMAT=NLNUM6. AS 'Volume traité en cumulé 'n, 
          t1.'COUNT_of_Cause Métier_Sum'n FORMAT=NLNUM6. AS 'Volume d''Autre cause en cumulé'n, 
          /* Taux dAutre Cause cumulé */
            (t1.'COUNT_of_Cause Métier_Sum'n/t1.COUNT_of_SGE_Sum) FORMAT=NLPCTI6. AS 'Taux dAutre Cause cumulé'n, 
          t1.'COUNT_of_Cause Commune1_Sum'n AS 'Volume traité en mensuel'n, 
          t1.'COUNT_of_Cause Commune_Sum'n AS 'Volume Autre Cause mensuel'n, 
          /* Taux mensuel */
            (t1.'COUNT_of_Cause Commune_Sum'n/t1.'COUNT_of_Cause Commune1_Sum'n) FORMAT=NLPCTI6. AS 'Taux mensuel'n
      FROM WORK.STABSUMMARYTABLESQUERY_TESTSGE t1;
	  proc format;
	  value mymissing " "="TOTAL"
	  run
QUIT;

Re: How to put "Total" in a column of stabsummary

Kurt_Bremser — Tue, 29 Nov 2016 11:07:59 GMT

proc format is a standalone procedure, so do not include it in the SQL.

If you want to have a format to have an effect, you have to use it somewhere. At the moment, I don't see where you use mymissing. And it should be $mymissing, as it is applied to a string.

So you have to

- execute proc format before the SQL

- check if your proc format has executed successfully

- use the format

Re: How to put "Total" in a column of stabsummary

peter2 — Tue, 29 Nov 2016 11:50:33 GMT

proc format;
	 
	value blankValue other='TOTAL';
run;

i try with that before proc sql now and nothing change...

my table is on this type

SGE Volume Volume Cause

Paris 1000 100

Marseille 500 200

1500 300

On the blank i want to see Total

I dont know if i have to use mymissing value or values Blankvalue

Re: How to put "Total" in a column of stabsummary

Kurt_Bremser — Tue, 29 Nov 2016 12:06:47 GMT

How did you assign your format to the variable?

Re: How to put "Total" in a column of stabsummary

Kurt_Bremser — Tue, 29 Nov 2016 12:33:53 GMT

And if you only want the value "Total" in SGE when SGE is empty, do

coalesce(SGE,'Total') as SGE

in the select of your SQL.

Re: How to put "Total" in a column of stabsummary

peter2 — Tue, 29 Nov 2016 13:11:49 GMT

Kurt, it's the stabsummary that make me the format for "All" automatically as you can see below.

ROC TABULATE
DATA=WORK.QUERY_TESTSGE

	OUT=WORK.STABSummaryTablesQUERY_TESTSGE(LABEL="Tables de synthèse pour WORK.QUERY_TESTSGE")
	
	;
	
	VAR COUNT_of_SGE "COUNT_of_Cause Métier"n "Part de la cause par SGE"n "COUNT_of_Cause Commune1"n "COUNT_of_Cause Commune"n "Poids de la cause sur mois"n;
	CLASS SGE /	ORDER=UNFORMATTED MISSING;
	TABLE /* Dimension de ligne */
SGE 
ALL={LABEL="Total"},

then i put the coalesce but it give me nothing

CREATE TABLE WORK.QUERY_FOR_STABSUMMARYTABLESQUERY(label="QUERY_FOR_STABSUMMARYTABLESQUERY_TESTSGE") AS

SELECT t1.SGE coalesce(SGE,'Total') as SGE,

t1.COUNT_of_SGE_Sum FORMAT=NLNUM6. AS 'Volume traité en cumulé 'n,

t1.'COUNT_of_Cause Métier_Sum'n FORMAT=NLNUM6. AS 'Volume d''Autre cause en cumulé'n,

/* Taux dAutre Cause cumulé */

(t1.'COUNT_of_Cause Métier_Sum'n/t1.COUNT_of_SGE_Sum) FORMAT=NLPCTI6. AS 'Taux dAutre Cause cumulé'n,

t1.'COUNT_of_Cause Commune1_Sum'n AS 'Volume traité en mensuel'n,

t1.'COUNT_of_Cause Commune_Sum'n AS 'Volume Autre Cause mensuel'n,

/* Taux mensuel */

(t1.'COUNT_of_Cause Commune_Sum'n/t1.'COUNT_of_Cause Commune1_Sum'n) FORMAT=NLPCTI6. AS 'Taux mensuel'n

FROM WORK.STABSUMMARYTABLESQUERY_TESTSGE t1;

QUIT;

Re: How to put "Total" in a column of stabsummary

Kurt_Bremser — Tue, 29 Nov 2016 13:21:25 GMT

Then SGE is not empty, or the coalesce would work.

Proof:

data have;
length sge $10;
sge = 'Paris'; output;
sge = 'Marseille'; output;
sge = ' '; output;
run;

proc print data=have; run;

proc sql;
create table want as
select coalesce(sge,'Total') as sge
from have;
quit;

proc print data=want; run;

Output:

 Obs    sge

  1     Paris    
  2     Marseille
  3              


 Obs    sge

  1     Paris    
  2     Marseille
  3     Total

Re: How to put "Total" in a column of stabsummary

peter2 — Tue, 29 Nov 2016 13:47:31 GMT

@Kurt, sorry but i dont express correctly my problem.

What i talk about is very commun, it's a automatic request make by SAS guide when you use "synthetis table".

When you want a total ("all"), the table doesnt recognise the name "All". It make a white.

What you explain me is surely right for you but i dont understand.

Re: How to put "Total" in a column of stabsummary

Kurt_Bremser — Tue, 29 Nov 2016 14:17:07 GMT

So your problem lies with the output of proc tabulate?

In the displayed results, you see "Total" (or whatever comes in yout locale), but in the table SAS leaves the class variable empty for summary rows.

(Makes sense, as there should be no misleading class value in a summary row).

So I applied my method to set the class variable to a quick summary of sashelp.class:

proc tabulate
  data=sashelp.class
  out=work.class_summary
;
var weight;
class sex / order=unformatted missing;
table
  sex all={label='Total'},
  weight * sum
;
run;

proc sql;
create table work.class_summary2 as
select
  coalesce(sex,'X') as sex,
  weight_sum
from work.class_summary
;
quit;

proc print data=work.class_summary2;
run;

Results:

 ----------------------------------------------
 |                               |   Weight   |
 |                               |------------|
 |                               |    Sum     |
 |-------------------------------+------------|
 |Sex                            |            |
 |-------------------------------|            |
 |F                              |      811.00|
 |-------------------------------+------------|
 |M                              |     1089.50|
 |-------------------------------+------------|
 |Total                          |     1900.50|
 ----------------------------------------------
                                               

                           Weight_
             Obs    sex      Sum

              1      F       811.0
              2      M      1089.5
              3      X      1900.5

I could not use a longer string, as sex is just $1, but it should illustrate the mechanism.

Re: How to put "Total" in a column of stabsummary

peter2 — Fri, 02 Dec 2016 09:08:22 GMT

Hello Kurt, thank you so much I I had not the time tout do what you say to
me but it's ok for the PROC SQL . can you tell me how can I do this for
proc report ?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: How to put "Total" in a column of stabsummary

Kurt_Bremser — Fri, 02 Dec 2016 09:16:59 GMT

Take a look at Paper: 242-2007: Advanced PROC REPORT-Doing More in the Compute Block, namely the section "CHANGING GROUPING VARIABLE VALUES ON SUMMARY LINES".

Re: How to put "Total" in a column of stabsummary

peter2 — Fri, 02 Dec 2016 09:20:22 GMT

Thank you I will read it later

##- Please type your reply above this line. Simple formatting, no
attachments. -##