Excel text format using Proc Tabulate

[rodrichiez]

Hi,

I'm trying to use the excel text format [tagattr="format:@"] in the column ID_NACIONAL but I noticed the format is applied to the columns definition even I didn't create the style element in that section. I've tried to put the statement in other sections of the code but I don't get to make it works fine.

PROC TABULATE 
DATA=WORK.MCR_CONSULTA
	
	;
	
	VAR IDRazon MONTO;
	CLASS DescRazon /	ORDER=UNFORMATTED MISSING;
	CLASS NOMBRE_DEM / 	ORDER=UNFORMATTED MISSING style=[width=2.5in];
	CLASS ID_NACIONAL / ORDER=UNFORMATTED MISSING;
	CLASS "APLICA"n /	ORDER=UNFORMATTED MISSING;
	TABLE 
		/* ROW Statement */
		NOMBRE_DEM  * ID_NACIONAL = 'ID_NACIONAL' * [style=[tagattr="format:@"]]  * "APLICA"n ,

		/* COLUMN Statement */
		DescRazon *(IDRazon=' ' * Sum=' ' MONTO=' ' * Sum=' ' )
		all = 'Total' * [style=[background=lightgrey fontweight=bold]] * (IDRazon=' ' * Sum=' ' MONTO=' ' * Sum=' ' * f=dollar10. );

 

RUN;

[ballardw]

There is an option on the Table statement in Proc Tabulate that might be related to your issue: FORMAT_PRECEDENCE. This option specifies the precedence of a format applied to a column, row or page controls and default is COLUMN.

I can't test anything as no example data was provided much less how the results are sent to Excel.

I would try changing:

		all = 'Total' * [style=[background=lightgrey fontweight=bold]] * (IDRazon=' ' * Sum=' ' MONTO=' ' * Sum=' ' * f=dollar10. );

to

	all = 'Total' * [style=[background=lightgrey fontweight=bold]] 
         * (IDRazon=' ' * Sum=' ' MONTO=' ' * Sum=' ' * f=dollar10. )
     / Format_Precedence=Row
   ;

the / starts table level options. I made the code a bit narrower so it was easier to read. I hate having to scroll horizontally to finish a line.

[Cynthia_sas]

Hi:
In addition to FORMAT_PRECEDENCE as a TABLE option, there is also an option for STYLE_PRECEDENCE, which indicates which style attributes should "win" in the case of conflicting style specifications.
Cynthia

[rodrichiez]

Hi,
I used format and style_precedence and didn't work. The style is applied to the column not to the rows.

[rodrichiez · Accepted solution]

Hello,

After many reading and investigation I've found the solution using classlev as follow:

PROC TABULATE
DATA=WORK.MCR_CONSULTA

;

VAR IDRazon MONTO;
CLASS DescRazon / ORDER=UNFORMATTED MISSING;
CLASS NOMBRE_DEM / ORDER=UNFORMATTED MISSING style=[width=2.5in];
CLASS ID_NACIONAL / ORDER=UNFORMATTED MISSING;
CLASSLEV ID_NACIONAL / S=[tagattr="format:@"];
CLASS "APLICA"n / ORDER=UNFORMATTED MISSING;
TABLE 
/* ROW Statement */
NOMBRE_DEM * ID_NACIONAL = 'ID_NACIONAL' * "APLICA"n,

/* COLUMN Statement */
DescRazon *(IDRazon=' ' * Sum=' ' MONTO=' ' * Sum=' ' )
all = 'Total' * [style=[background=lightgrey fontweight=bold]] * (IDRazon=' ' * Sum=' ' MONTO=' ' * Sum=' ' * f=dollar10.);



RUN;

 CLASSLEV can be use to specify format to the values of a class variable.