topic Re: "file print ods" formatting. in ODS and Base Reporting

"file print ods" formatting.

ChrisNZ — Thu, 21 May 2009 03:56:49 GMT

This is a 2-stage question. I think stage 1 might be easier, but I could be wrong.

1- Is there a way to apply traffic-lighting (color) in a data step that writes to an (html) ODS destination (no, I cannot use a procedure)?

eg I want Alice in red:

ods html file='f:\ddd.htm' style=seaside;
data _null_;
set sashelp.class;
file print ods=(variables=( _N_ age name));
put _ods_ ;
run;
ods html close;

2-Also, is there a way to reuse the stylesheet's style so the first column _N_ uses the header formatting (bold on yellow background here) ?

Thank you for your help.

Re: "file print ods" formatting.

Cynthia_sas — Thu, 21 May 2009 14:59:03 GMT

Hi:
If you use a table template with DATA _NULL_ and FILE PRINT ODS, then you can perform the traffic-lighting and highlighting (using style elements) that you describe.
Refer to this paper:
http://www2.sas.com/proceedings/sugi30/088-30.pdf
Programs are downloadable from:
http://support.sas.com/rnd/papers/sugi30/data_null.zip

There are other examples in the ODS FAQ, if you search the Tech Support site and the ODS tip sheet for DATA _NULL_ is:
http://support.sas.com/rnd/base/ods/scratch/table-tips.pdf

cynthia

Re: "file print ods" formatting.

ChrisNZ — Thu, 21 May 2009 23:40:53 GMT

oooh! Beautiful! Thanks Cynthia.

Stage 2 solved, but stage 1 not quite: using a template before hand would require that the red values are known.

The red values are determined in the data step, so I need a way to format on the fly. I do it by inserting <span style=></span> around the value at the moment, which is a good-enough workaround for html output.

Any better way?

Message was edited by: C. Graffeuille Message was edited by: C. Graffeuille

Re: "file print ods" formatting.

ChrisNZ — Fri, 22 May 2009 00:35:17 GMT

Oh, one last tip please: I need to output several tables from my data step. How do I tell ODS that a new table starts? I tried a number of solutions, like providing a new object name in successive file print ods statements, but I didnt succeed.

Re: "file print ods" formatting.

Cynthia_sas — Fri, 22 May 2009 05:46:48 GMT

Hi:
I'm not sure what you mean by a new table??? Will this work for you:
[pre]
ods html file='twotable.html';

data step for table 1;

data step for table 2;

ods html close;

[/pre]

The table template does not support BY group processing as far as I know -- so if you wanted a new table for every BY group, I believe that you'd need a separate data step for every BY group -OR- of course, you could macro-ize your data step program.

About your other question about how to set the color dynamically, I would think that some combination of CELLSTYLE...AS with user-defined formats might be dynamic enough for you.

cynthia

[pre]
** example of using data step logic to set;
** traffic lighting values;
ods path work.temps(update)
sashelp.tmplmst(read);

proc template;
define table twocol;
column name dc age;
define name;
header = 'Name';
end;
define dc;
print=on;
*print=off;
end;
define age;
header = 'Age';
cellstyle dc = 'reg' as {background=pink},
dc = 'hi' as {background=red},
dc = 'both' as {background=blue foreground=yellow},
1 as Data;
end;
end;
run;

ods html file='c:\temp\dyncolr.html' style=sasweb;
data _null_;
length dc $4;
set sashelp.class;

** use data step logic to decide;
** what background color a variable gets;
** the DC variable can be used in CELLSTYLE...AS;
** to "Decide Color" -- after making sure it works;
** turn PRINT=OFF in table template;
if sex = 'F' then dc = 'reg';
else if sex = 'M' then dc = 'hi';
if sex = 'M' and age = 16 then dc = 'both';
if age = 15 then dc = ' ';

file print ods=(template='twocol'
columns=(name
dc
age)
);
put _ods_;
run;
ods html close;

[/pre]

Re: "file print ods" formatting.

ChrisNZ — Tue, 26 May 2009 21:24:45 GMT

Thanks a lot again, Cynthia, the dynamic color assignment logic is interesting, I have to look at it.

Regarding the new table, I would be after something like:
[pre]
ods html file='twotable.html';

data step ;

file print ods statement for table x;
...logic to populate table1 (probably involving loop, array, hash table)
file print ods statement for table x+1;
...logic to populate table2

ods html close;
[/pre]
where 2 new tables are written out at each iteration, for example

Re: "file print ods" formatting.

Cynthia_sas — Wed, 27 May 2009 01:30:46 GMT

Hi:
I don't think your way will work (produce 2 tables with one data step). The table template is ONLY going to make one table per template. Since the data step does not perform BY group processing your only choices are:

[pre]
ods html file='twotable.html';
data step for table 1;

data step for table 2;
ods html close;
[/pre]

OR, if both tables are using the SAME table template and you can define the logical terms for creating each table separately, you could:

[pre]
ods html file='twotable.html';
macro invocation to generate both tables
ods html close;
[/pre]

as shown below.

cynthia

[pre]
%macro dotbbl(tabnum=1, whcls=);

title "Table Number: &tabnum";
title2 "Criteria: &whcls";
%let varlist=;

%if &tabnum = 1 %then %do;
%let varlist = name sex height;
%end;
%else %if &tabnum = 2 %then %do;
%let varlist = sex name height age;
%end;
%else %if &tabnum = 3 %then %do;
%let varlist = name sex age height weight;
%end;

%put -----> varlist is &varlist ;

** of course, your data _null_ step could;
** be more complicated and use a custom table template;

data _null_;
set sashelp.class;
where &whcls;
file print ods=(variables=( &varlist ));
put _ods_;
run;

%mend dotbbl;

options nosymbolgen nomprint nomlogic;
ods html file='maketables.html' style=sasweb;
%dotbbl(tabnum=1, whcls = age eq 13);
%dotbbl(tabnum=2, whcls = sex eq 'F');
%dotbbl(tabnum=3, whcls = name contains 'Al');
ods html close;

[/pre]

Re: "file print ods" formatting.

ChrisNZ — Wed, 27 May 2009 21:54:31 GMT

Thank you very much for your comprehensive and clear replies, Cynthia.

I'll stick to writing HTML directly it seems as all the calculations do need to happen in one data step. No big deal, it is clean and it works. 🙂

A few rough things that could be better about the technical format of this forum:
- One has to type the < code to type <. Not good!
- A 'Code' button inserting [ pre ] tags would be nice
- The 'Quote' button's action is crude compared to normal forum behaviour
- Copying and pasting your code to sas (XP) pastes a single line. Going thru wordpad restores the line breaks, but loses the indentation.
Just my 0.02

Thanks again.

Re: "file print ods" formatting.

Cynthia_sas — Wed, 27 May 2009 23:26:57 GMT

Hi:
All good and valid points. Here's how I deal with them.

The code pasting issue is sort of a pain, but here's how I get around it:
1) Cut and paste the code from the Forum into Word.
2) Word apparently recognizes the line breaks in the code that's inside the [ pre] and [/ pre] tags
3) Then cut and paste from Word into SAS and SAS recognizes the line breaks from Word -and- respects the indentation.

I have just learned to live with < < oddity. That's what find and replace is designed for. I never use the Quote button. I have the pre tags in a notepad file, I cut and paste them from there.

cynthia

Re: "file print ods" formatting.

ChrisNZ — Thu, 28 May 2009 20:56:07 GMT

Hi Cynthia,

I was hoping you'd be able to pass this on to the java developer who maintains the web site. 🙂

Cheers

Re: "file print ods" formatting.

Cynthia_sas — Thu, 28 May 2009 21:11:01 GMT

Hi:
Everything you noted has already been shared.
cynthia

Re: "file print ods" formatting.

ChrisNZ — Thu, 28 May 2009 21:52:11 GMT

Thank you!