The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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


Accepted Solutions
Solution
‎05-02-2016 07:46 PM
SAS Super FREQ
Posts: 8,645

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

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

View solution in original post


All Replies
Grand Advisor
Posts: 9,325

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

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 ;



New Contributor
Posts: 3

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

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





Solution
‎05-02-2016 07:46 PM
SAS Super FREQ
Posts: 8,645

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

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
New Contributor
Posts: 3

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

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.

Grand Advisor
Posts: 9,325

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

Can't FOOTNOTE '---------' or TITLE '----------' get it ? Post a picture to show what you want.
SAS Super FREQ
Posts: 8,645

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

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;
SAS Super FREQ
Posts: 8,645

Re: how to use different numeric formats in ods tagsets.excelxp in proc tabulate [SEC=UNCLASSIFIED]

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
Post a Question
Discussion Stats
  • 7 replies
  • 470 views
  • 0 likes
  • 3 in conversation