BookmarkSubscribeRSS Feed
peter2
Obsidian | Level 7

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;

 

 

11 REPLIES 11
Kurt_Bremser
Super User

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
Obsidian | Level 7
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

peter2
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

@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
Super User

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
Obsidian | Level 7
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. -##
peter2
Obsidian | Level 7
Thank you I will read it later

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1585 views
  • 1 like
  • 2 in conversation