Using the mtPrivatizeVars macro in htmSQL

Here's how I would use the macro in an htmSQL page. The page is designed to display arbitrary data sets (for a particular data set, I could just hard code everything), and assumes that variables LIBRARY and TABLE have been passed as parameters. I'm not showing the queery statement or surrounding HTML here:

{sql}
select   resolve('%mtprivatizesqlvars(data={&LIBRARY}.{&TABLE})') 
            as selectstring length=32000
from     dictionary.columns
where    libname = upcase('{&LIBRARY}') 
         and upcase(memname) = upcase('{&TABLE}') 
         and memtype = 'DATA'
         and varnum = 1
{/sql}

{sql}
select    {&SELECTSTRING}
from     {&LIBRARY}.{&TABLE}
{/sql}

<table border="1">
   <tr><th>Name</th>{&SYS.COLNAME[*] before="<th>" between="</th><th>" after="</th>"}</tr>
   <tr><th>Label</th>{label var="{&SYS.COLNAME[*]}" before="<th>" between="</th><th>" after="</th>"}</tr>
  {eachrow}
   <tr><th>{&SYS.QROW}</th>{&{&SYS.COLNAME[*]} before="<td>" between="</td><td>" after="</td>"}</tr>
  {/eachrow}
</table>

The first block of code captures the output of the mtprivatizesql macro into an htmSQL variable. The macro must have been stored in a macro search path known to the htmSQL processor. The key here is the use of the RESOLVE function, which returns the results of evaluating a string. I'm assuming a reasonable number of variables (something which is under my control), so the results will fit into the selectstring length of 32,000 bytes. By the way, this doesn't work in SAS 8.2, where you'll get only the first 200 characters of the result.

The second block of code selects the records to display.

The third block of code displays the records. I've chosen to display two header lines, the first showing the variable names and the second showing the variable labels.

By the way, there are other checks, not shown, which prevent the end user from changing the URL to display an arbitrary table anywhere on the system.