Desktop productivity for business analysts and programmers

How to put "Total" in a column of stabsummary

Reply
Contributor
Posts: 31

How to put "Total" in a column of stabsummary

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;

 

 

Super User
Posts: 7,432

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

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

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

Super User
Posts: 7,432

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

How did you assign your format to the variable?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,432

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

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

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;

 

Super User
Posts: 7,432

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

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    
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

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

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

Super User
Posts: 7,432

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

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

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. -##
Super User
Posts: 7,432

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 31

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

Thank you I will read it later

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Ask a Question
Discussion stats
  • 11 replies
  • 251 views
  • 1 like
  • 2 in conversation