Simple (clear) formatting in ExcelXP tagsets output
in [SAS]
|
Prev: solutions book
Next: Merge two table in single report
From: Adam on 24 Jul 2010 06:37 Hi All, I'm using ExcelXP tagsets (SAS 9.1.3) to output to an XML file which I can load straight into Excel, which works a treat. However, the default formatting is a bit cumbersome with really large row and column text and dark grey cell backgrounds. I'd like to clear this up. I thought maybe I could specify another style in the ODS statement, but that results in an error when opening the file in Excel. I have ended up with this: proc tabulate data=testdata style=[background=white borderwidth=2]; class status; classlev status / style=[background=white font_size=10pt font_weight=bold just=center borderwidth=2]; var andiff buhdiff werdiff wimdiff alldiff / style=[background=white font_size=10pt font_weight=bold just=center borderwidth=2]; keyword all sum / style=[background=white font_size=10pt font_weight=bold just=center borderwidth=2]; freq yeargross; format status stat.; table (andiff buhdiff werdiff wimdiff alldiff)*sum*f=nlmnlgbp20.0 , status="" all / row=float box=[label=" " style=[background=white]]; run; ....but it's very cumbersome and generates massive documents. Can anyone tell me how to avoid all this extra code and/or make the style= option work in the ODS statement? (Also my just=center attributes don't seem to work and the spreadsheet ends up filled with "numbers stored as text" rather than just "numbers"!) Thanks a lot for any help, Adam
From: Ya on 24 Jul 2010 11:50 On Jul 24, 3:37 am, Adam <n...(a)snowstone.org.uk> wrote: > Hi All, > > I'm using ExcelXP tagsets (SAS 9.1.3) to output to an XML file which I > can load straight into Excel, which works a treat. However, the > default formatting is a bit cumbersome with really large row and > column text and dark grey cell backgrounds. I'd like to clear this up. > I thought maybe I could specify another style in the ODS statement, > but that results in an error when opening the file in Excel. I have > ended up with this: > > proc tabulate data=testdata style=[background=white borderwidth=2]; > class status; > classlev status / style=[background=white font_size=10pt > font_weight=bold just=center > borderwidth=2]; > var andiff buhdiff werdiff wimdiff alldiff / > style=[background=white > font_size=10pt font_weight=bold just=center > borderwidth=2]; > keyword all sum / style=[background=white font_size=10pt > font_weight=bold just=center > borderwidth=2]; > freq yeargross; > format status stat.; > table (andiff buhdiff werdiff wimdiff alldiff)*sum*f=nlmnlgbp20..0 > , status="" all > / row=float box=[label=" " style=[background=white]]; > run; > > ...but it's very cumbersome and generates massive documents. Can > anyone tell me how to avoid all this extra code and/or make the style= > option work in the ODS statement? (Also my just=center attributes > don't seem to work and the spreadsheet ends up filled with "numbers > stored as text" rather than just "numbers"!) > > Thanks a lot for any help, > Adam What happens if you try style-minimal? ods tagsets.excelXp file="cccc" style=minimal; prc tabulate... Ya
From: Adam on 26 Jul 2010 07:20 On 24 July, 16:50, Ya <huang8...(a)gmail.com> wrote: > On Jul 24, 3:37 am, Adam <n...(a)snowstone.org.uk> wrote: > What happens if you try style-minimal? > > ods tagsets.excelXp file="cccc" style=minimal; > > prc tabulate... I get an error from Excel when I try and load the file: Problems came up in the following areas during load: Workbook Setting Style Table The file cannot be opened because of errors... Thanks, Adam
From: Joseph on 26 Jul 2010 16:27 On Jul 26, 7:20 am, Adam <n...(a)snowstone.org.uk> wrote: > On 24 July, 16:50, Ya <huang8...(a)gmail.com> wrote: > > > On Jul 24, 3:37 am, Adam <n...(a)snowstone.org.uk> wrote: > > What happens if you trystyle-minimal? > > > ods tagsets.excelXp file="cccc"style=minimal; > > > prc tabulate... > > I get an error from Excel when I try and load the file: > > Problems came up in the following areas during load: > Workbook Setting > Style > Table > The file cannot be opened because of errors... > > Thanks, > Adam I just experienced this problem myself. Style = Minimal is creating the error. Change it to something else.
From: Lou on 26 Jul 2010 19:44
>"Adam" <news(a)snowstone.org.uk> wrote in message news:897920a8-c0cc-4e2b-a336-dbd9bbf150bd(a)w30g2000yqw.googlegroups.com... >On 24 July, 16:50, Ya <huang8...(a)gmail.com> wrote: >> On Jul 24, 3:37 am, Adam <n...(a)snowstone.org.uk> wrote: > >> What happens if you try style-minimal? >> >> ods tagsets.excelXp file="cccc" style=minimal; >> >> prc tabulate... > >I get an error from Excel when I try and load the file: > >Problems came up in the following areas during load: > Workbook Setting > Style > Table >The file cannot be opened because of errors... > >Thanks, >Adam I had the same problem, using SAS 9.1.3 - depending on the style used, Excel would either open the file or refuse to open the file with an error message. The remedy was to go to http://support.sas.com/rnd/base/ods/odsmarkup/, download the latest version of the tagset and install it. Everything now works fine. |