topic Re: Trying to output a table in HTML via a Macro in SAS Programming

Trying to output a table in HTML via a Macro

Filipvdr — Mon, 10 Jan 2011 12:53:57 GMT

Hello,

i'm trying to generate a table in HTML, this is my code:
the problem is that it generates really the name of the variable and not the name fo the value it has in my data set:

[pre]

%macro makeHTMLtable(table=,width=);
/* Calculate the number of variables in dataset and putting them in a variable seperated by spaces */
proc contents data = &table out = vars(keep = varnum name) noprint;
run;
proc sql noprint;
select distinct name into :orderedvars separated by ' ' from vars order by varnum;
quit;
%let numberofvariables=%sysfunc(countw(&orderedvars));
%put &orderedvars &numberofvariables;
data _null_;
file _webout ;
set &table end=oef;
/* Header Row */
if _N_ = 1 then do;
put "";
put "";
CounterVariable = 1;
do while(CounterVariable le &numberofvariables);
Variable = scan("&orderedvars",CounterVariable,' ');
put "";
CounterVariable = CounterVariable + 1;
end;
put "";
end;
/* DataRow */
put "";
CounterVariable = 1;
do while(CounterVariable le 3);
Variable = scan("&orderedvars",CounterVariable,' ');
put "";
CounterVariable = CounterVariable + 1;
end;
put "";
if oef then put '

" Variable;
put "

';
run;
%mend;
%makeHTMLtable(table=prio_table_small,width=100%);

[/pre]

Re: Trying to output a table in HTML via a Macro

chang_y_chung_hotmail_com — Mon, 10 Jan 2011 14:44:47 GMT

A quick fix is to replace two lines in the second do while loop like so:

OLD:

Variable = scan("&orderedvars",CounterVariable,' ');

put "<td>" Variable;

NEW:

Variable = scan("&orderedvars",CounterVariable,' ');

Value = vvaluex(Variable);

put "<td>" Value;

Re: Trying to output a table in HTML via a Macro

deleted_user — Mon, 10 Jan 2011 15:12:05 GMT

hello,

another solution is to build the variable "Variable" as a macro variable:

%macro makeHTMLtable(table=,width=);

/* Calculate the number of variables in dataset and putting them in a variable seperated by spaces */
proc contents data = &table out = vars(keep = varnum name) noprint;
run;

proc sql noprint;
select distinct name into :orderedvars separated by ' ' from vars order by varnum;
quit;

%let numberofvariables=%sysfunc(countw(&orderedvars));

%put &orderedvars &numberofvariables;

data _null_;

file _webout ;
set &table end=oef;
/* Header Row */
if _N_ = 1 then do;
put "";
put "";
%let CounterVariable = 1;
%do %while(%eval(&CounterVariable) le %eval(&numberofvariables));
%let Variable = %scan(&orderedvars,&CounterVariable,' ');
put "";
%let CounterVariable = %eval(&CounterVariable + 1);
%end;
put "";
end;

/* DataRow */
put "";
%let CounterVariable = 1;
%do %while(%eval(&CounterVariable) le %eval(3));
%let Variable = %scan(&orderedvars,&CounterVariable,' ');
put "";
%let CounterVariable = %eval(&CounterVariable + 1);
%end;
put "";

if oef then put '

" &Variable.;
put "

';

run;
%mend;

Marius

Re: Trying to output a table in HTML via a Macro

Filipvdr — Tue, 11 Jan 2011 11:41:05 GMT

Ok, that worked, so now im a bit further and i have an other problem

The loop for the rowfilters isn't working correct, it always crashes..

[pre]
%macro makeHTMLtable(table=,width=,keepvariables=,rowfilters=);
/* Calculate the number of variables in dataset and putting them in a variable seperated by spaces if the value keepvariables is not given*/
%if &keepvariables eq '' %then %do;
proc contents data = &table out = vars(keep = varnum name) noprint;
run;
proc sql noprint;
select distinct name into :orderedvars separated by ' ' from vars order by varnum;
quit;
%let numberofvariables=%sysfunc(countw(&orderedvars));
%end;
%else %do;
%let numberofvariables=%sysfunc(countw(&keepvariables));
%end;
data _null_;
file _webout ;
set &table end=oef;
/* Header Row */
if _N_ = 1 then do;
put "";
put "";
CounterVariable = 1;

do while(CounterVariable le &numberofvariables);
if &keepvariables eq '' then Variable = scan("&orderedvars",CounterVariable,' ');
else Variable = scan(&keepvariables,CounterVariable,' ');
put "";
CounterVariable = CounterVariable + 1;
end;
put "";
end;
/* DataRow */
/* Filters */
if &rowfilters ne ' ' then do;
nrfilters = count(&rowfilters,',');
do while(CounterFilter le nrfilters);
FullFilter = scan(&rowfilters,CounterFilter,',');
field = scan(FullFilter,1,' ');
value = scan(FullFilter,2,' ');
rowstyle = scan(FullFilter,3,' ');
if vvaluex(field) eq value then put "";
end;
end;
/*else put "";*/

CounterVariable = 1;
do while(CounterVariable le &numberofvariables);
if &keepvariables eq '' then Variable = scan("&orderedvars",CounterVariable,' ');
else Variable = scan(&keepvariables,CounterVariable,' ');
Value = vvaluex(Variable);
put "";
CounterVariable = CounterVariable + 1;
end;

put "";
if oef then put '

" Variable;
put "

" Value;
put "

';
run;
%mend;
%makeHTMLtable(table=prio_table_small,width=50%,keepvariables="Lot_Id Facility",rowfilters="facility PLINE200 red,Lot_Id AL100439 orange,"); [/pre]

Re: Trying to output a table in HTML via a Macro

sbb — Tue, 11 Jan 2011 15:40:42 GMT

To the OP: have you explored a SAS ODS generated output rather than hand-coding a DATA/PROC/macro-generated solution? Can you explain what it is about the required format that is sufficiently unique to need to go about it this way?

Scott Barry
SBBWorks, Inc.

Re: Trying to output a table in HTML via a Macro

Cynthia_sas — Tue, 11 Jan 2011 16:25:04 GMT

Hi:
I agree with Scott. What you're doing doesn't make sense. ODS can write HTML 4.01 tags, HTML 3.2 tags, Microsoft HTML tags, WML tags, to name just a few of the HTML-related markup tags that ODS can write for you.

I notice that you are passing in a WIDTH so you can set width on your <TABLE> tag. ODS, for example, will do that for you. If you use the programs below, the OUTPUTWIDTH=80% style attribute for PROC PRINT will cause
[pre]
width: 80%;
[/pre]

to be written to the TABLE tag in the generated output. If you are trying to generate HTML without an embedded CSS section or without an HTML <HEAD> section, ODS HTML has the NO_TOP_MATTER and NO_BOTTOM_MATTER suboptions that will generate the HTML in this manner, too.

cynthia
[pre]
options center;
ods listing close;

** be sure your browser window is maximized so you can see the difference;
** look at created HTML files with NOTEPAD to see the HTML tags created;
ods html file='c:\temp\usewidth_50.html' style=sasweb;

proc print data=sashelp.prdsale(obs=10)
style(table)={outputwidth=50%};
title 'OUTPUTWIDTH=50%';
run;

ods html close;

** create microsoft-friendly HTML tags;
ods msoffice2k file='c:\temp\usewidth_80.html' style=sasweb;

proc print data=sashelp.prdsale(obs=10)
style(table)={outputwidth=80%};
title 'OUTPUTWIDTH=80%';
run;

ods msoffice2k close;

** create HTML 3.2 tags;
ods html3 file='c:\temp\usewidth_100.html' style=sasweb;

proc print data=sashelp.prdsale(obs=10)
style(table)={outputwidth=100%};
title 'OUTPUTWIDTH=100%';
run;

ods html3 close;

** use NO_TOP_MATTER and NO_BOTTOM_MATTER;
ods html file='c:\temp\use_notop.html' (NO_TOP_MATTER) style=sasweb;

proc print data=sashelp.prdsale(obs=10);;
title 'use NO_TOP_MATTER';
run;

ods html close;

ods html file='c:\temp\use_nobot.html' (NO_BOTTOM_MATTER) style=sasweb;

proc print data=sashelp.prdsale(obs=10);;
title 'use NO_BOTTOM_MATTER';
run;

ods html close;
[/pre]

Re: Trying to output a table in HTML via a Macro

chang_y_chung_hotmail_com — Tue, 11 Jan 2011 17:18:22 GMT

I don't think any pre-existing ODS destination would give a clean HTML snippet that OP wants to generate and to flush out into _webout.

But you can do this with a very simple (in fact, some would say, elementary) custom tagset that listens to only a few events.

As it is, a variable can take a maximum of one row and one column (equality) filters. Thus, there can be as many row/column filters as the number of variables. (Can you see why?)

It is really easy to modify this tagset so that it can take multiple row/column filters for each var. This is left as an exercise for those who are interested. 🙂

It may be a bit more involved to generalize this so that the "filter" accommodates non-equality as well.

For those who are trying, here is a hint:

It would involve users writing the filters using a marker (or a place holder) for the variable in the expression,

so that the tagset can replace the marker with actual value of the cell and evaluate the expression.

A natural marker may be the variable name itself.
This is not really that involved at all.

P.S. Please don't be alarmed by the red-colored keywords that look like errors ("undefined keywords") -- they are not.

Come on, the editor dev team!
This has been available all along ...

/* prepend a template store, work.myTmp to ods path */

ods path (prepend) work.myTmp(update);

/* create a tagset, hfTab - html table with "filtering" */

proc template;

define tagset tagsets.hfTab;

/* ods events */

define event option;

set $name upcase(name);

set $options[$name] value; /* collect options specified */

end;

define event table_body;

start:

trigger openTable;

trigger doHeaders;

finish:

trigger closeTable;

end;

define event colspec_entry;

break /if cmp(name, 'obs'); /* ignore the obs column */

set $names[] name; /* collect column names */

end;

define event row;

start: /* some init for each row */

break /if ^$inTable;

unset $trClasses;

unset $tds;

finish:

break /if ^$inTable;

unset $class; /* open tr with classes, if any */

iterate $trClasses;

do /while _value_;

set $class catx(' ', $class, _value_);

next $trClasses;

done;

put "<tr";

put " class='" $class "'" /if ^missing($class);

put ">";

iterate $tds; /* output cells in the row */

do /while _value_;

put _value_;

next $tds;

done;

putl "</tr>"; /* close tr tag */

end;

define event data;

set $name upcase(name);

/* if column name matches a column filter then add a class attribute */

do /if $cfClasses[$name];

set $tds[] "<td class='" $cfClasses[$name] "'>" value "</td>";

else;

set $tds[] "<td>" value "</td>";

done;

/* if the cell matches a row filter then collect the class name */

set $trClasses[] $rfClasses[$name] /if cmp($rfValues[$name], value);

end;

/* user events */

define event openTable;

put "<table";

put " width=" quote($options["WIDTH"]) /if $options["WIDTH"];

putl ">";

trigger parseRowFilters /if $options["ROWFILTERS"];

trigger parseColFilters /if $options["COLFILTERS"];

set $inTable "True";

end;

define event doHeaders;

put "<tr>";

iterate $names;

do /while _value_;

set $name upcase(_value_);

do /if $cfClasses[$name];

put "<th class='" $cfClasses[$name] "'>" _value_ "</th>";

else;

put "<th>" _value_ "</th>";

done;

next $names;

done;

putl "</tr>";

end;

define event closeTable;

putl "</table>";

unset $options;

unset $inTable;

unset $names;

end;

/* for parsing filter options */

define event parseRowFilters;

set $str $options["ROWFILTERS"];

trigger getList;

iterate $list;

do /while _value_;

set $var scan(_value_, 1);

set $var upcase($var);

set $rfValues[$var] scan(_value_, 2);

set $rfClasses[$var] scan(_value_, 3);

next $list;

done;

unset $list;

end;

define event parseColFilters;

set $str $options["COLFILTERS"];

trigger getList;

iterate $list;

do /while _value_;

set $var scan(_value_, 1);

set $var upcase($var);

set $cfClasses[$var] scan(_value_, 2);

next $list;

done;

unset $list;

end;

/* utility */

define event getList;

/* given $str of a delimited list, returns a list, $list[] */

break /if missing($str);

set $dlm ",";

eval $i 1;

set $item scan($str, $i, $dlm);

do /while ^missing($item);

set $list[] strip($item);

eval $i $i + 1;

set $item scan($str, $i, $dlm);

done;

end;

/* quote those (in)famous five */

map = %nrstr("<>&'""");

mapsub = %nrstr("/</>/&/'/"/");

end;

run;

/* usage */

ods tagsets.hfTab file=_webout

options(width='100%' colfilters='weight weight'

rowfilters='sex F girl, name Thomas thomas, age 12 twelve');

proc print data=sashelp.class;

where age <= 12;

run;

ods tagsets.hfTab close;

/* _webout

<tr><th>Name</th><th>Sex</th><th>Age</th><th>Height</th><th class='weight'>Weight</th></tr>

<tr class='twelve'><td>James</td><td>M</td><td>12</td><td>57.3</td><td class='weight'> 83.0</td></tr>

<tr class='girl'><td>Joyce</td><td>F</td><td>11</td><td>51.3</td><td class='weight'> 50.5</td></tr>

<tr class='girl twelve'><td>Louise</td><td>F</td><td>12</td><td>56.3</td><td class='weight'> 77.0</td></tr>

<tr class='twelve'><td>Robert</td><td>M</td><td>12</td><td>64.8</td><td class='weight'>128.0</td></tr>

<tr class='thomas'><td>Thomas</td><td>M</td><td>11</td><td>57.5</td><td class='weight'> 85.0</td></tr>

</table>

/* clean up */

ods path reset;

Message edited: "Filtering" implemented. by chang_y_chung@hotmail.com on 2011-01-12

Re: Trying to output a table in HTML via a Macro

Cynthia_sas — Tue, 11 Jan 2011 17:58:38 GMT

Hi:
Well, CHTML and PHTML destinations come really close to very sparse HTML -- especially the CHTML destination.

cynthia

Re: Trying to output a table in HTML via a Macro

sbb — Tue, 11 Jan 2011 18:10:46 GMT

In my experience with ODS-generated output, it's much easier to parse SAS-generated HTML for any required manipulation than having to maintain a SAS program to generate the HTML tag statements for whatever is being created. I can't imagine having to turn this type of code-piece over to someone else, expecting that individual to embrace the same technical approach, frankly.

Scott Barry
SBBWorks, Inc.

Re: Trying to output a table in HTML via a Macro

Filipvdr — Wed, 12 Jan 2011 06:48:20 GMT

Well i am writing this macro so we could output any table to HTML and give some "rules" to it. For example when column A is equal to valueA then make it a red row and so on...

We want to be able to re-use this macro for any kind of report that we will have to make in the future.

So everybody can generate this output easy by giving parameters like table=,width=,keepvariables=,rowfilters=,colfilters= without writing 100 lines of code

Re: Trying to output a table in HTML via a Macro

sbb — Wed, 12 Jan 2011 13:39:46 GMT

To the OP: what you are asking can be done with ODS and cell-highlighting. I would encourage you to abandon the "roll your own HTML" technique, where possible.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

conditional cell highlighting site:sas.com

Re: Trying to output a table in HTML via a Macro

Cynthia_sas — Wed, 12 Jan 2011 16:17:22 GMT

Hi:
You can do what you want to do with PROC REPORT, specifically, inside a COMPUTE block with a CALL DEFINE statement, as shown
http://support.sas.com/forums/thread.jspa?messageID=8815≯
http://support.sas.com/forums/thread.jspa?messageID=43726꫎
http://support.sas.com/forums/thread.jspa?messageID=44917꽵

You can even use a FORMAT for the background color of the row -- as shown in the code below. This program can be made even more dynamic, if you want, by using the actual data to generate the format (using CNTLIN features of PROC FORMAT). But for simplicity of example, the trafficlighting was kept simple here.

cynthia
[pre]
proc format;
value RFMT 0-49999 = 'yellow'
50000-69999 = 'pink'
70000-high = 'cyan';
run;

title;
ods listing close;
ods html file='c:\temp\rep_hilite.html' style=sasweb;
proc report data=sashelp.prdsale nowd;
by country;
title '1) PROC REPORT Highlight COLUMN In Simple STYLE= Override';
column country division prodtype actual;
define country /group ;
define division/group;
define prodtype / group;
define actual / sum 'Sales'
style(column) = {background=RFMT.};
run;

proc report data=sashelp.prdsale nowd;
by country;
title '2) PROC REPORT Highlight _ROW_ In CALL DEFINE';
column country division prodtype actual;
define country /group ;
define division/group;
define prodtype / group;
define actual / sum 'Sales';
compute actual;
bcolor = catt('style={background=',put(actual.sum,Rfmt.),'}');
call define(_ROW_,'STYLE',bcolor);
endcomp;
run;
ods html close;
[/pre]

Re: Trying to output a table in HTML via a Macro

Filipvdr — Mon, 17 Jan 2011 12:21:23 GMT

ok, i followed your advice a little bit and tried some things out with proc report.

It is less code, but it is less flexibel. I have some questions to ask.

My Output: http://img140.imageshack.us/img140/4729/outputv.jpg
My Questions:
1. How can i get the by variable not printed out in between?
2. Why are there spaces before Holdcode (tested with 2 different styles)
3. How does my link gets opened in a new window?
4. Can i add formatting to my text in the compute before part?

My Code:
[pre]
ods html file=_webout style=sasweb;

proc report data=prio_table nowd; /* title 'Testing Outputs'; */
column Facility Lot_id Route Holdcode Holdnote Execby ProcessEngineer BatchEngineer Holdcode;
define Lot_id /group;
define Facility / group;
by Lot_id ;
compute before _page_;
text1 = "Holdcode: " || strip(Holdcode) || " Holdnote: " || strip(Holdnote) || "" ;
text2 = "ProcessEngineer: " || strip(ProcessEngineer) || " BatchEngineer: " || strip(BatchEngineer) || "" ;

line text1 $;
line text2 $;
endcomp;

compute Lot_id ;
urlstring = "http://www.sas.com/";
if Lot_id ne ' ' then
call define (_row_,'STYLE','style={background=orange}');
CALL DEFINE(_COL_, 'URL', urlstring );
CALL DEFINE(_COL_, 'STYLE','style={hreftarget=_blank}' );

endcomp;
compute Execby ;
if Execby eq 'PLINE' then
call define (_row_,'STYLE','style={background=red}');
endcomp;

run;
ods html close;
[/pre]

Re: Trying to output a table in HTML via a Macro

sbb — Mon, 17 Jan 2011 15:08:59 GMT

Some pertinent reply info:

1. How can i get the by variable not printed out in between?
REPLY: OPTIONS NOBYLINE;

2. Why are there spaces before Holdcode (tested with 2 different styles)
REPLY: suggest self-diagnosis by reviewing the SAS-generated HTML code - nothing evident from JPEG image.
3. How does my link gets opened in a new window?
REPLY: You must manipulate your generated URL string to include the HTML-standard tag behavior below:
target="_blank"
4. Can i add formatting to my text in the compute before part?
REPLY: suggested SAS support website search below.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

+"proc report" +"compute before" +formatting site:sas.com

Re: Trying to output a table in HTML via a Macro

Cynthia_sas — Mon, 17 Jan 2011 18:47:16 GMT

Hi:
Scott has some good suggestions. Specifically, about your COMPUTE BEFORE, if you put a STYLE override on the COMPUTE statement itself, the override will ONLY apply to text written with the LINE statement(s) within that COMPUTE block:
[pre]
ods listing close;
ods html file='c:\temp\output\testcp.html' style=sasweb;

proc report data=sashelp.class nowd;
compute before _page_ /
style={foreground=purple just=l font_weight=bold font_size=12pt};
line 'this is a line in purple';
endcomp;
run;
ods html close;
[/pre]

To turn off the BYLINE, investigate the NOBYLINE system option.

cynthia

Re: Trying to output a table in HTML via a Macro

Filipvdr — Tue, 18 Jan 2011 07:55:29 GMT

3. How does my link gets opened in a new window?
REPLY: You must manipulate your generated URL string to include the HTML-standard tag behavior below:
target="_blank"

this does not work for me?

@Cynthia:

Can i use more then one compute before _page_?

For example the 2 first lines can be in black.
The third line has to be red when the if condition is true

Re: Trying to output a table in HTML via a Macro

sbb — Tue, 18 Jan 2011 12:28:53 GMT

To the OP: you will need to share a bit more information than:

"this does not work for me?"

What code did you use to make the change?

What HTML tag information was generated by SAS to the HTML document?

Scott Barry
SBBWorks, Inc.

Re: Trying to output a table in HTML via a Macro

Filipvdr — Tue, 18 Jan 2011 12:55:12 GMT

compute Lot_id ;
url = 'do?_program=/Samples/SP_ShortTermScheduleFast&lotnumber=' || strip(Lot_id) || '&_odsstyle=meadow';
if Lot_id ne ' ' then
call define (_row_,'STYLE','style={background=orange}');
CALL DEFINE(_COL_, 'URL', url );
CALL DEFINE(_COL_, 'STYLE','style={hreftarget=_blank}' );
endcomp;

Re: Trying to output a table in HTML via a Macro

Cynthia_sas — Tue, 18 Jan 2011 15:17:42 GMT

Hi:
Are you using PROC REPORT to start up SASStoredProcess/do??? So you are doing all this within the context of the BI platform?? (That may change how different client apps use style overrides and WRS used to have issues with LINE statement output (not liking it)).

If you are coding the URL= directly in the URL attribute for PROC REPORT, I do not know whether the STYLE attribute for HREFTARGET will override or append to what's already been coded in the URL attribute. (Your experience seems to prove that it does NOT work.) But anyway, that would be a question for Tech Support. You may have to construct your fully qualified URL for the URL attribute including target= in HTML syntax.

You can only have 1 COMPUTE BEFORE _PAGE_ -- but you can use ODS ESCAPECHAR functionality to change your text string from the default. Note how line 2 in the code below uses ODS ESCAPECHAR to make the second line red and in a smaller font than what is specified in the COMPUTE BEFORE style override.

cynthia
[pre]
ods listing close;
ods html file='c:\temp\output\testcp.html' style=sasweb;
ods escapechar='^';

proc report data=sashelp.class nowd;
compute before _page_ /
style={foreground=purple just=l font_weight=bold font_size=12pt};
newvar = '^{style[foreground=red font_size=8pt]2 ) This is a line with the line in red}';
line '1) this is a line in purple';
line newvar $100.;
endcomp;
run;
ods html close;
[/pre]

Re: Trying to output a table in HTML via a Macro

Ksharp — Wed, 19 Jan 2011 02:53:03 GMT

>3. How does my link gets opened in a new window?

Yes. proc report can do it.Need the proper style.

[pre]

ods html file='c:\temp\testcp.html' style=sasweb;
ods escapechar='^';

proc report data=sashelp.class nowd ;
compute before _page_ ;
line '1) this is a line in purple';
line '^S={ url="http://www.sas.com" hreftarget="_blank"}' 'This is a link with an open window';

endcomp;
run;
ods html close;
[/pre]

Ksharp