How to put "Total" in a column of stabsummary

[peter2]

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;

[Kurt_Bremser]

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

[peter2]

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

[Kurt_Bremser]

How did you assign your format to the variable?

[Kurt_Bremser]

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.

[peter2]

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;

[Kurt_Bremser]

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    

[peter2]

@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.

[Kurt_Bremser]

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.

[peter2]

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. -##

[Kurt_Bremser]

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".

[peter2]

Thank you I will read it later

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