how to use different numeric formats in ods tagsets.excelxp in proc tabulate

[Mostlyclueless]

Hi

I am using sas version 9.1.3.

I want my proc tabulate to format some numeric variables using the format # ##0 (ie 17 324)and other numeric variables to have the format 12.1 (ie -8.4). This is the code I have tried:

ods tagsets.excelxp file='test.xml' style=custom ;

proc tabulate data=a  style=[tagattr='format:# ##0'];

   class year ;

   var x y z  ;

   var xx ;

   table year=' ', x y z xx / misstext='0' row=float ;

run ;

ods tagsets.excelxp close ;

which is close to what I want but of course does not give the desired format for the variable xx.

I then tried

ods tagsets.excelxp file='test2.xml' style=custom ;

proc tabulate data=a ;

   class year ;

   var x y z / style = {tagattr='format:# ##0'} ;

   var xx / style = {tagattr='format:12.1'};

   table year=' ', x y z xx / misstext='0' row=float ;

run ;

ods tagsets.excelxp close ;

This produces the undesirable result of formatting all variables with two decimal places and no separator ie 6112.46.

Any assistance greatly appreciated.

Thanks

C

[Ksharp]

TRY:


proc tabulate data=a ;
   class year ;
var x y z  ;
   var xx ;
   table year=' ', x*{style = {tagattr='format:# ##0'} } 
y*{style = {tagattr='format:# ##0'} } 
z*{style = {tagattr='format:# ##0'} }
 xx / misstext='0' row=float ;
run ;

[Mostlyclueless]

Thank you. This works for the variables x y and z but not for xx. I want xx to be formatted as -7.4 for example. Also there is a longer list of variables in the var statement. Will I need to specify the *{style = {tagattr='format:# ##0'} } for each of them?

Caroline

[Cynthia_sas · Accepted solution]

Hi, The ONLY place you can touch the format for individual variables is in the TABLE statement. So the answer is yes, if you want your XX and other variable to be formatted, using TAGATTR, you will have to use the STYLE syntax in the TABLE statement for each one you want to have TAGATTR specified for.

cynthia

[Mostlyclueless]

Thanks so much Cynthia. Another question I have is: how do I add a horizontal line above and below the table? I have tried various options and nothing gives a horizontal line above the headings and a horizontal line below the last row, ie just above the footnotes.

[Ksharp]

Can't FOOTNOTE '---------' or TITLE '----------' get it ? Post a picture to show what you want.

[Cynthia_sas]

Have you tried style=journal in your ODS statement (where you specify FILE=)?
ods xxxx file='yyy.xxxx' style=journal;
proc whatever;
run;
ods xxxx close;

[Cynthia_sas]

Ah, although I did not focus on your destination. I am not sure that the border lines that you want will work in ODS TAGSET.EXCELXP. Excel is notoriously picky about borderlines.

cynthia