how to dynamic html with long mixed datas set (lots of special characters)

[TimMandell]

I've create a table in a stored process like the following

    proc sql ;
    create table %bquote(usr_dat.)&_USERNAME as
    select
    monotonic() as rwnm,
    trim(CODE_TYPE) as code_type,
    trim(CODE)as code,
    trim(MODIFIER)as modifier,
    trim(DESCRIPTION) as description,
    trim(RESPONSE) as response,
    trim(ADD_CODE) as add_code,
    trim(ADD_MODIFIER) as add_modifier,
    ADD_LINE_NUMBER,
    trim(COMMENT) as comment

    from dat.rvw_data
    &whr1 ;
    quit;

I would like output to the macro processor that is like:

    put '<tD><input name=""ln",monotonic() ,""" type=""text"" value =""", monotonic() ,""" size=""3"" tabindex=""-1"" readonly /></tD>';
    put '<tD><input name=""ct",monotonic() ,""" type=""text"" value =""", trim(CODE_TYPE) ,""" size=""10"" tabindex=""-1"" readonly /></tD>';
    put '<tD><input name=""cd",monotonic() ,""" type=""text"" value =""", trim(CODE) ,""" size=""10"" tabindex=""-1"" readonly /></tD>';
    put '<tD><input name=""md",monotonic() ,""" type=""text"" value =""", trim(MODIFIER) ,""" size=""6"" tabindex=""-1"" readonly /></tD>';
    put '<tD><input name=""dc",monotonic() ,"""type=""text"" value =""", trim(DESCRIPTION) ,""" size=""100"" tabindex=""-1"" readonly /></tD>';
    put '<tD ><select name=""asp" ,monotonic(),""" style=""width: 200px;"" > <option value=""" , trim(RESPONSE) , """>" ,  trim(RESPONSE) , "</option> </select></tD>';
    put '<tD ><select name=""acd" ,monotonic(),""" style=""width: 50px;"" > <option value=""" , trim(ADD_CODE) , """>" ,  trim(ADD_CODE) , "</option> </select></tD>';
    put '<tD><input name=""amd",monotonic() ,""" type=""text"" value =""" , trim(ADD_MODIFIER) ,""" size=""6"" /></tD>';
    put '<tD><input name=""aln",monotonic() ,""" type=""text"" value =""" , ADD_LINE_NUMBER ,""" size=""2"" /></tD>';
    put '<tD><input name=""asC",monotonic() ,""" type=""text"" value =""" , trim(COMMENT) ,""" size=""60"" ></tD>';

I can do something like this in proc SQL with CAT as calling the variable but I run into the buffer issue

200 characters in WHERE clauses and/or PROC SQL.

I would like to learn how to accomplish this in macro processor so I can take full advantage of:

65534 characters when called from the macro processor.

[TimMandell]

I'm able to output to the macro facility below is the solution for output to macro facility by building off the before mentioned table made via proc sql.

I'm not sure how to handle special characters using this approach.

For instance we have quotes marks and lots of other fun items in the COMMENT column

    proc sql ;
    select
    cat("put '<tr name = ""row",rwnm,""" >';"),
    cat("put '<tD><input name=""ln",rwnm,""" type=""text"" value =""", rwnm ,""" size=""3"" tabindex=""-1"" readonly /></tD>';"),
    cat("put '<tD><input name=""ct",rwnm ,""" type=""text"" value =""", trim(CODE_TYPE) ,""" size=""10"" tabindex=""-1"" readonly /></tD>';"),
    cat("put '<tD><input name=""cd",rwnm ,""" type=""text"" value =""", trim(CODE) ,""" size=""10"" tabindex=""-1"" readonly /></tD>';"),
    cat("put '<tD><input name=""md",rwnm ,""" type=""text"" value =""", trim(MODIFIER) ,""" size=""6"" tabindex=""-1"" readonly /></tD>';"),
    cat("put '<tD><input name=""dc",rwnm ,"""type=""text"" value =""", trim(DESCRIPTION) ,""" size=""100"" tabindex=""-1"" readonly /></tD>';"),
    cat("put '<tD ><select name=""asp" ,rwnm,""" style=""width: 200px;"" > <option value=""" , trim(RESPONSE) , """>" ,  trim(RESPONSE) , "</option> </select></tD>';"),
    cat("put '<tD ><select name=""acd" ,rwnm,""" style=""width: 50px;"" > <option value=""" , trim(ADD_CODE) , """>" ,  trim(ADD_CODE) , "</option> </select></tD>';"),
    cat("put '<tD><input name=""amd",rwnm ,""" type=""text"" value =""" , trim(ADD_MODIFIER) ,""" size=""6"" /></tD>';"),
    cat("put '<tD><input name=""aln",rwnm ,""" type=""text"" value =""" , ADD_LINE_NUMBER ,""" size=""2"" /></tD>';"),
    cat("put '<tD><input name=""asC",rwnm ,""" type=""text"" value =""" , trim(COMMENT) ,""" size=""60"" ></tD>';")
    into
     :rw_lst1 - :rw_lst&m_ln,
     :ln_lst1 - :ln_lst&m_ln,
     :ct_lst1 - :ct_lst&m_ln,
     :cd_lst1 - :cd_lst&m_ln,
     :md_lst1 - :md_lst&m_ln,
     :dc_lst1 - :dc_lst&m_ln,
     :asp_lst1 - :asp_lst&m_ln,
     :acd_lst1 - :acd_lst&m_ln,
     :amd_lst1 - :amd_lst&m_ln,
     :aln_lst1 - :aln_lst&m_ln,
     :asc_lst1 -  :asc_lst&m_ln
     from %bquote(usr_dat.)&_USERNAME;
    quit;

and later it is called back into the macro processor as shown where m_ln is the number of observations in the data set.

%do q= 1 %to &m_ln;
   &&rw_lst&q
   &&ln_lst&q
   &&ct_lst&q
   &&cd_lst&q
   &&md_lst&q
   &&dc_lst&q
   &&asp_lst&q
   &&acd_lst&q
   &&amd_lst&q
   &&aln_lst&q
   &&asc_lst&q
%end;

[AllanBowe]

I urge you to avoid such quoting gymnastics entirely by separating your data extraction (backend) from the presentation layer (frontend).  

See tip 1 of these 5 Tips for SAS App Developers.

The macro language simply cannot handle certain special characters - for instance those used to enquote (http://support.sas.com/kb/36/916.html).

You'll save yourself much head banging by keeping your COMMENT column in the PDV and writing it to your frontend using `proc json` (or with the Boemska h54s Adapter).

[TimMandell]

I agree there should be one macro for representation and another for data side though the entirety of the "app" will be deployed in SAS Stored Procedure server.  So to me one macro v. many macros doesn't change the fundamentals just the read ability.

Using JSON and later JQUERY to interact the data with HTML is a leaning curve it seems I'll have to get passed to overcome special characters.

Given:

"The macro language simply cannot handle certain special characters - for instance those used to enquote (http://support.sas.com/kb/36/916.html)."

I'm not sure there is a really solution due to the environment I'm working in.

[TimMandell]

I couldn't follow the json path it just didn't click.

The below code represents how to loop through macro variable in a put statement thus forgoing CAT and the buffer issue of trying to use put in the SQL statement.

Still having problems when special characters, esp quotes, that are in the macro array variables.

asc_LST array that has all kinds of characters due to being a results from user free text entries.

%nrbquote() is something I'm testing that does not work on the asc_LST array and have not proven that is works on all values from each of the other arrays.

All assistance is greatly appreciated. Thank you

%if %sysevalf(%superq( prov1 ) ne , boolean) 
 and %sysevalf(%superq( mrn1 ) ne , boolean) 
 and %sysevalf(%superq( dos1 ) ne , boolean)   %then %do;
   put
     %DO L =1 %TO &M_LN;
     cmt = htmlencode("&&asc_LST&L");
     '<tr name = "row' "&&LN_LST&L" '" >
      <tD><input type="text" name="ln' "&&LN_LST&L" '" value ="' "%nrbquote(&&LN_LST&L)" '" size="3" tabindex="-1" readonly /></tD>
      <tD><input type="text" name="ct' "&&LN_LST&L" '" value ="' "%nrbquote(&&ct_LST&L)" '" size="10" tabindex="-1" readonly /></tD>
     <tD><input type="text" name="cd' "&&LN_LST&L" '" value ="' "%nrbquote(&&cd_LST&L)" '" size="10" tabindex="-1" readonly /></td>
     <tD><input type="text" name="md' "&&LN_LST&L" '" value ="' "%nrbquote(&&md_LST&L)" '" size="6" tabindex="-1" readonly /></td>
     <tD><input type="text" name="dc' "&&LN_LST&L" '" value ="' "%nrbquote(&&dc_LST&L)" '" size="100" tabindex="-1" readonly /></td>
     <tD ><select name="asp' "&&LN_LST&L" '" style= "width: 200px;"><option value="' "%nrbquote(&&asp_LST&L)" '">' "%nrbquote(&&asp_LST&L)" '</option> </select></td>
     <tD ><select name="acd' "&&LN_LST&L" '" style= "width: 50px;"><option value="' "%nrbquote(&&acd_LST&L)" '">' "%nrbquote(&&acd_LST&L)" '</option> </select></td>
     <tD><input type="text" name="amd' "&&LN_LST&L" '" value ="' "%nrbquote(&&amd_LST&L)" '"size="6" /></td>
     <tD><input type="text" name="aln' "&&LN_LST&L" '" value ="' "%nrbquote(&&aln_LST&L)" '"size="2" /></td>
     <tD><input type="text" name="asC' "&&LN_LST&L" '" value ="' "&&asc_LST&L" '"size="100" /></td>
   </tr>'
   %END;
;
%end;

[AllanBowe]

My point was - rather than trying to write a HTML file directly, try hosting your front end (HTML, CSS, JavaScript) on the mid-tier and sending data to it via the HTML5 for SAS Adapter.

A quick guide to this approach is here:  https://www.rawsas.com/2015/12/building-web-apps-with-sas/

[TimMandell]

I don't disagree with your approach it doesn't seem to fit into our set up.

We only have a managed SAS BI interface for our web interface.

Managed meaning security and environment are a request to tech support and not direct - no SAS BI Management Console available to developers.

No Apache server or the like. 

To my understanding of SAS BI

The "middle tier" is the Stored process because it is the only method to get SAS BI to accept all web languages in a programmable way with the most flexibility being the " data _null_; file _webout; " method.

In this fashion everything should be called by macro processor to allow divisions of work. This can be portioned by creating many stored processes and call to each from a master stored process or all macro's in one file.

When reading over the website provided I'm not certain how it fits into our setup but it does give examples of using Java that is greatly appreciated.

[TimMandell]

I found a method to handle the special characters. BUT I'm too much of a rookie to get the data back into the macro processor with out it being reinterpreted.  Ideas?

 data hmm ;
 set  dt;
 length a10 $32767;
  a1 = strip(rwnm);
  a2 = strip(CODE_TYPE);
  a3 = strip(htmlencode(CODE,'quot amp gt lt apos 7bit'));
  a4 = strip(MODIFIER);
  a5 = strip(DESCRIPTION);
  a6 = strip(htmlencode(RESPONSE,'quot amp gt lt apos 7bit'));
  a7 = strip(ADD_CODE);
  a8 = strip(ADD_MODIFIER);
  a9 = strip(ADD_LINE_NUMBER);
  a10 = strip(htmlencode(COMMENT,'quot amp gt lt apos 7bit'));
 Run;

I tried the following but it just reads the &quot; and other replaced characters as sas code

 proc sql;
 select
  cat("put '<tr name = ""row", strip(a1) ,""" >';"),
  cat("put '<tD><input name=""ln", strip(a1) ,""" type=""text"" value =""" , strip(a1) ,""" size=""3"" tabindex=""-1"" readonly /></tD>';"),
  cat("put '<tD><input name=""ct", strip(a1) ,""" type=""text"" value =""" , strip(a2) ,""" size=""10"" tabindex=""-1"" readonly /></tD>';"),
  cat("put '<tD><input name=""cd", strip(a1) ,""" type=""text"" value =""" , strip(a3) ,""" size=""10"" tabindex=""-1"" readonly /></tD>';"),
  cat("put '<tD><input name=""md", strip(a1) ,""" type=""text"" value =""" , strip(a4) ,""" size=""6"" tabindex=""-1"" readonly /></tD>';"),
  cat("put '<tD><input name=""dc", strip(a1) ,"""type=""text"" value =""" , strip(a5) ,""" size=""100"" tabindex=""-1"" readonly /></tD>';"),
  cat("put '<tD><select name=""asp", strip(a1) ,""" style=""width: 200px,"" > <option value =""" , strip(a6)  , """>" ,  strip(a6) , "</option> </select></tD>';"),
  cat("put '<tD><select name=""acd", strip(a1) ,""" style=""width: 50px,"" >  <option value =""" , strip(a7) , """>" ,  strip(a7) , "</option> </select></tD>';"),
  cat("put '<tD><input name=""amd", strip(a1) ,""" type=""text"" value =""" , strip(a8) ,""" size=""6"" /></tD>';"),
  cat("put '<tD><input name=""aln", strip(a1) ,""" type=""text"" value =""" , strip(a9) ,""" size=""2"" /></tD>';"),
  cat("put '<tD><input name=""asC", strip(a1) ,""" type=""text"" value =""" , strip(a10) ,""" size=""60"" ></tD> </tr>';")
 into 
  :rw_lst1- :rw_lst&m_ln,
  :ln_lst1 - :ln_lst&m_ln,
  :ct_lst1 - :ct_lst&m_ln,
  :cd_lst1 - :cd_lst&m_ln,
  :md_lst1 - :md_lst&m_ln,
  :dc_lst1 - :dc_lst&m_ln,
  :asp_lst1 - :asp_lst&m_ln,
  :acd_lst1 - :acd_lst&m_ln,
  :amd_lst1 - :amd_lst&m_ln,
  :aln_lst1 - :aln_lst&m_ln,
  :asc_lst1 - :asc_lst&m_ln 
 from hmm;

%do q = 1 %to &m_ln;
  &&rw_lst&q;
  &&ln_lst&q;
  &&ct_lst&q;
  &&cd_lst&q;
  &&md_lst&q;
  &&dc_lst&q; 
  &&asp_lst&q;
  &&acd_lst&q;
  &&amd_lst&q;
  &&aln_lst&q;
  &&asc_lst&q;

%end;

[TimMandell · Accepted solution]

thanks to:

Russ Tyndall

SAS Technical Support Analyst

SAS Certified Advanced Programmer for SAS 9

at SAS support here is a complete answer:

*as before handle the special characters in a data step while creating the macro varables;
			data _null_;
				set  dt;
						call symputx('b'||LEFT(_N_),strip(rwnm));
						call symputx('c'||LEFT(_N_),strip(CODE_TYPE));
						call symputx('d'||LEFT(_N_),strip(htmlencode(CODE,'quot amp gt lt apos 7bit')));
						call symputx('e'||LEFT(_N_),strip(MODIFIER));
						call symputx('f'||LEFT(_N_),strip(DESCRIPTION));
						call symputx('g'||LEFT(_N_),strip(htmlencode(RESPONSE,'quot amp gt lt apos 7bit')));
						call symputx('h'||LEFT(_N_),strip(ADD_CODE));
						call symputx('i'||LEFT(_N_),strip(ADD_MODIFIER));
						call symputx('j'||LEFT(_N_),strip(ADD_LINE_NUMBER));
						call symputx('k'||LEFT(_N_),strip(htmlencode(COMMENT,'quot amp gt lt apos 7bit')));
			Run;

*now use Tslit and %NRBQUOTE liberally to handle the quoting that is needed around the final statement;

		%do q = 1 %to &m_ln;
put %tslit(<tr name ="row&&b&q" >);
put %tslit(<tD><input name="ln&&b&q" type="text" value ="&&b&q" size="3" tabindex="-1" readonly /></tD>);
put %tslit(<tD><input name="ct&&b&q" type="text" value ="&&c&q" size="10" tabindex="-1" readonly /></tD>);
put %tslit(<tD><input name="cd&&b&q" type="text" value ="%NRBQUOTE(&&d&q)" size="10" tabindex="-1" readonly /></tD>);
put %tslit(<tD><input name="md&&b&q" type="text" value ="&&e&q" size="6" tabindex="-1" readonly /></tD>);
put %tslit(<tD><input name="dc&&b&q" type="text" value ="&&f&q" size="100" tabindex="-1" readonly /></tD>);
put %tslit(<tD><select name="asp&&b&q" style="width: 200px," > <option value ="%NRBQUOTE(&&g&q)" >%NRBQUOTE(&&g&q) </option> </select></tD>);
put %tslit(<tD><select name="acd&&b&q" style="width: 50px," > <option value ="&&h&q">&&h&q </option> </select></tD>);
put %tslit(<tD><input name="amd&&b&q" type="text" value ="&&i&q" size="6" /></tD>);
put %tslit(<tD><input name="aln&&b&q" type="text" value ="%NRBQUOTE(&&j&q)" size="2" /></tD>);
put %tslit(<tD><input name="asC&&b&q" type="text" value ="%NRBQUOTE(&&k&q)" size="60" ></tD> </tr>);
	%end;