Trying to output a table in HTML via a Macro

[Filipvdr]

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 "

" Variable; put "

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

[/pre]

[chang_y_chung_hotmail_com]

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;

[deleted_user]

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 "

" &Variable.; put "

';

run;
%mend;

Marius

[Filipvdr]

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]

[sbb]

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.

[Filipvdr]

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

[sbb]

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

[Cynthia_sas]

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]

[Filipvdr]

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]

[sbb]

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

[Cynthia_sas]

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

[Filipvdr]

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

[sbb]

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.

[Filipvdr]

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;

[Cynthia_sas]

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]