Using htmSQL in a batch SAS job
One of my interests lately has been in figuring out ways to create master/detail (invoice/lineitem, etc.) reports in SAS. I've concentrated on what can be done in base SAS.
Although it's not a base product, SAS/Intrnet also has a way to produce master/detail reports - the htmSQL processor. It takes an HTML page marked up with special tags and expands the tags using data from SAS. This is typically done though a web server, but if you have SAS/Intrnet licensed you can use the module outside of the web. You can produce only HTML (or other text-based output), but even so this has the potential to be a powerful tool for certain uses. This usage is documented, but I haven't seen a usage in the wild, so I'm posting an example.
I haven't done anything at all fancy with the HTML; it's just the bare minimum. Also, this doesn't show the hoops I had to jump through to get all the file permissions set right, but they would be site-dependent anyway.
%let htmsqlpgm = /opt/nethome1/datawebapps/sas-cgi/htmSQL;
/**** Set up data ****/
options compress=no nocenter nodate nonumber;
libname me '/usr/users/hamiltja';
data me.Invoices;
infile cards;
input @1 Invoice_Num 3.
@5 Customer $14.
@20 Invoice_Date date9.;
format Invoice_Date worddatx12.;
cards;
101 Hugo Furst 01Jan2004
102 Freida Peeples 15Jan2004
103 Al E. Loohah 30Jan2004
;;;;
data items;
infile cards;
length Item $17.;
input @1 Item $10.
@12 Cost 10.2;
format cost comma10.2;
cards;
Widget 12.00
Gadget 10.00
Frammet 36.00
Gizmo 50.37
Whatsit 100.00
ItsIts 1.00
Thingie 37.56
Sprocket 13.00
Geehaws 5.76
HooHaas 22.22
DingDing 37.54
Thing 79.43
HoopyDoopy 40.01
Mairsy 67.66
Doat 77.21
Doesy 85.31
Divy 49.37
;;;;
data me.LineItems;
keep Invoice_Num Line_Num Item Cost;
set me.invoices (keep=invoice_num);
length size1-size5 $6.;
length item $30.;
array sizes{5} size1-size5 (' ' 'Small' 'Medium' 'Large' 'Jumbo');
Line_Num = 0;
do i = 1 to 6;
if ranuni(95819) > .2 then
do;
set items (rename=(Item=ItemName Cost=ItemCost))point=i;
do j = 1 to 5;
if ranuni(40324) > .2 then
do;
size = sizes(j);
item = catx(' ', size, itemname);
cost = itemcost * j;
line_num + 1;
output;
end;
end;
end;
end;
run;
proc datasets library=me nolist;
modify LineItems;
index create Invoice_Num;
run; quit;
/**** The Real Work ****/
/* Create a file containing the htmSQL we want to use. */
filename hsql '/usr/users/hamiltja/batchweb.hsql';
data _null_;
infile cards;
file hsql;
input;
put _infile_;
cards4;
<html>
<body>
<h1>Invoices and Items</h1>
{query server="mtshrtst"}
{library sqlname="invoice" path="/usr/users/hamiltja"}
{sql}
select Invoice_Num,
Customer,
Invoice_Date
from invoice.invoices
order by invoice_num
{/sql}
{eachrow}
<h2>Invoice {&INVOICE_NUM} for {&CUSTOMER} dated {&INVOICE_DATE}</h2>
{sql}
select Line_Num,
Item,
Cost
from invoice.lineitems
where invoice_num = {&INVOICE_NUM}
order by line_num
{/sql}
<table border="1">
<tr>{label var="{&sys.colname[*]}"
before="<th>"
between="</th><th>"
after="</th>"}</tr>
{eachrow}
<tr>{&{&sys.colname[*]}
before="<td>"
between="</td><td>"
after="</td>"}</tr> {/eachrow}
<table>
{/eachrow}
{/query}
</body>
</html>
;;;;
filename htmsql pipe
"&HTMSQLPGM. /usr/users/hamiltja/batchweb.hsql '_debug=0'";
filename webout '/usr/users/hamiltja/batchweb.html';
/* Run program through pipe. Throw away first four lines of output, */
/* which contain header information used by browsers. */
data _null_;
file webout;
infile htmsql lrecl=1024 firstobs=4;
input;
put _infile_;
run;
Here's the first part of the output, with extraneous blank lines deleted:
<html>
<body>
<h1>Invoices and Items</h1>
<h2>Invoice 101 for Hugo Furst dated 01JAN2004 </h2>
<table border="1">
<tr><th>Line_Num</th><th>item</th><th>cost</th></tr>
<tr><td> 1</td><td>Widget</td><td> 12</td></tr>
<tr><td> 2</td><td>Small Widget</td><td> 24</td></tr>
<tr><td> 3</td><td>Medium Widget</td><td> 36</td></tr>
<tr><td> 4</td><td>Small Gadget</td><td> 20</td></tr>
0 Comments:
Post a Comment
<< Home