topic Re: Excel text format using Proc Tabulate in SAS Programming

Excel text format using Proc Tabulate

rodrichiez — Wed, 13 Mar 2019 21:13:52 GMT

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;

Re: Excel text format using Proc Tabulate

ballardw — Wed, 13 Mar 2019 23:10:15 GMT

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. );

	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.

Re: Excel text format using Proc Tabulate

Cynthia_sas — Thu, 14 Mar 2019 00:07:36 GMT

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

Re: Excel text format using Proc Tabulate

rodrichiez — Thu, 14 Mar 2019 12:28:36 GMT

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

Re: Excel text format using Proc Tabulate

rodrichiez — Thu, 14 Mar 2019 13:39:49 GMT

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.