BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TimMandell
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
TimMandell
Obsidian | Level 7

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;

View solution in original post

8 REPLIES 8
TimMandell
Obsidian | Level 7

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
Barite | Level 11

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).

 

 

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
TimMandell
Obsidian | Level 7

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
Obsidian | Level 7

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
Barite | Level 11

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/

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
TimMandell
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1749 views
  • 1 like
  • 2 in conversation