Prev: solutions book
Next: Merge two table in single report
From: Adam on 29 Jul 2010 08:55
Thanks all for the comments. Unfortunately I can't alter the SAS
environment as I'm in a "corporate" setting with all of the computers
locked-down to the same settings. I couldn't find a working
alternative built-in style.

After a good deal of trial and error, I've ended up using this code to
alter the default style:

/* Set up a temporary location to write the template to */
ods path work.temptemp(update) sasuser.templat(read)
sashelp.tmplmst(read);

/* Create a custom template to make Excel output clearer */
proc template;
define style excelstyle;

parent = styles.default;

replace Data from Cell /
foreground = cx000000
background = cxFFFFFF
tagattr = 'format:#,##0'
borderwidth = 2;

replace Header from HeadersAndFooters /
foreground = cx000000
background = cxFFFFFF
just = c
font = ("Arial, Helvetica, sans-serif", 10pt, Bold)
borderwidth = 2;

replace RowHeader from Header /
just = l;

replace SystemTitle from TitlesAndFooters /
foreground = cx000000
font = ("Arial, Helvetica, sans-serif", 12pt);

end;
run;

This seems to be working fine, but with one execption:
I use 'misstext="0"' in the proc tabulate. There are lots of cells in
my tables with missing values so this creates a lot of "0"s in the
spreadsheet. The problem is that Excel keeps complaining about "number
stored as text". This causes Excel to take ages opening the file and
puts a load of untidy marks all over the cells.Is there any way to use
a number instead of text for the misstext value?

Thanks,
Adam
From: Lou on 30 Jul 2010 08:34

"Adam" <news(a)snowstone.org.uk> wrote in message
news:5c101563-cb3a-439c-8075-c31cdc463920(a)i31g2000yqm.googlegroups.com...
> Thanks all for the comments. Unfortunately I can't alter the SAS
> environment as I'm in a "corporate" setting with all of the computers
> locked-down to the same settings. I couldn't find a working
> alternative built-in style.

If you can create a template as you show below, you can download and use the
updated tagset. One of the files included in the download is called
"excltags.tpl". If you include it in your program with a line like

%include excltags.tpl;

it will set things up in your SASUSER template store. When it comes time to
produce your output, you do something like

ods listing close;
ods tagsets.ExcelXP body = outdd style = normalprinter;

"normalprinter" is a SAS supplied style. If used with an older tagset, it
creates XML files that Excel can't open. The style (and everyother I've
tried) works fine with the new tagset. When you're done, you use:

ods tagsets.ExcelXP close;
ods listing;

If you want to clear your template store after you're done using the tagset,
code the following:

proc template;
delete tagsets.config_debug;
delete tagsets.excelbase;
delete tagsets.excelxp;
quit;

None of this interferes with the corporate configuration, you're just
writing to your personal template store essentially in the same way you're
doing now. And you really need only one line of code in your program (the
%include statement) to use the tagset.

The ideal solution would be to have whoever supports SAS on the IT side at
your company update the system templates, but we're talking of
possibilities.

> After a good deal of trial and error, I've ended up using this code to
> alter the default style:
>
> /* Set up a temporary location to write the template to */
> ods path work.temptemp(update) sasuser.templat(read)
> sashelp.tmplmst(read);
>
> /* Create a custom template to make Excel output clearer */
> proc template;
> define style excelstyle;
>
> parent = styles.default;
>
> replace Data from Cell /
> foreground = cx000000
> background = cxFFFFFF
> tagattr = 'format:#,##0'
> borderwidth = 2;
>
> replace Header from HeadersAndFooters /
> foreground = cx000000
> background = cxFFFFFF
> just = c
> font = ("Arial, Helvetica, sans-serif", 10pt, Bold)
> borderwidth = 2;
>
> replace RowHeader from Header /
> just = l;
>
> replace SystemTitle from TitlesAndFooters /
> foreground = cx000000
> font = ("Arial, Helvetica, sans-serif", 12pt);
>
> end;
> run;
>
> This seems to be working fine, but with one execption:
> I use 'misstext="0"' in the proc tabulate. There are lots of cells in
> my tables with missing values so this creates a lot of "0"s in the
> spreadsheet. The problem is that Excel keeps complaining about "number
> stored as text". This causes Excel to take ages opening the file and
> puts a load of untidy marks all over the cells.Is there any way to use
> a number instead of text for the misstext value?
>
> Thanks,
> Adam


From: Adam on 8 Aug 2010 17:39
On 30 July, 13:34, "Lou" <lpog...(a)hotmail.com> wrote:
> "Adam" <n...(a)snowstone.org.uk> wrote in message
>
> news:5c101563-cb3a-439c-8075-c31cdc463920(a)i31g2000yqm.googlegroups.com...
>
> > Thanks all for the comments. Unfortunately I can't alter the SAS
> > environment as I'm in a "corporate" setting with all of the computers
> > locked-down to the same settings. I couldn't find a working
> > alternative built-in style.
>
> If you can create a template as you show below, you can download and use the
> updated tagset.  One of the files included in the download is called
> "excltags.tpl".  If you include it in your program with a line like
[snip useful stuff]

Thanks, that's helpful :)
Adam
First  |  Prev  | 
Pages: 1 2
Prev: solutions book
Next: Merge two table in single report