topic Re: issues parasing a single quote in SAS Programming

issues parasing a single quote

RustyOwens — Tue, 07 Nov 2023 14:02:09 GMT

Hello. I came here a few weeks ago because I was working on a solution where I was generating HTML to update some tables in SAS. Thanks to the sas community, I am 99% complete. I have one use case that refuses to work.

put " <INPUT TYPE='Text' NAME='CompanyName' width='50' value=""" "&EscapedValue.""" maxlength='100'><br>";

In the one use case that fails, the value returned from the database contains a single quote. I have tried everything I can to escape it. I can't replace with an ascii equivalent because I use that value to join to another table and it needs to match the value there. Any suggestions would be appreciated.

Re: issues parasing a single quote

Tom — Tue, 07 Nov 2023 14:18:53 GMT

What exactly are you having trouble with?

I am not sure how a database comes into this problem. You appear to be just showing data step code to write part of an HTML file. And it does not even reference any dataset variables.

What do have in the macro variable ESCAPEDVALUE?

What do you want written to the HTML file?

Re: issues parsing a single quote

RustyOwens — Tue, 07 Nov 2023 14:23:32 GMT

My apologies. Here is the is the full code.

proc printto new log='/sas/config/Lev1/SASApp/SASEnvironment/SASCode/Edit_Company.log';
libname National '/vol2/mars/national';

/* %macro extract_id(value);
%let id = %scan(&value, -1, _);
%put The extracted id is: &id;
%mend extract_id; */

/* Example usage */
/* * %extract_id(AAP_REQUEST_123); */

/* Utilize the parameter */
proc sql noprint;
select Companyname into :CompanyName
from National.Company
where id = &id;
quit;

%let EscapedCompanyName = %sysfunc(tranwrd(%sysfunc(dequote("&CompanyName.")), "'", "''"));

/* Assign the value of the escaped Companyname to a new variable */
%let EscapedCompanyName = %sysfunc(tranwrd(%sysfunc(dequote("&CompanyName.")), "'", "''"));

/* Assign the value of the escaped Companyname to a new variable */
%let EscapedValue = &EscapedCompanyName;

data _null_;
file _webout;

put '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">';
put '<html>';
put '<head>';
put '<title>National Accounts Oneline Program</title> ';
put '<meta name="Author" content="Crystal">';
put '<meta name="Description" content="National Accounts Oneline Program">';
put '<link rel="stylesheet" type="text/css" href="S:\Workgroups\SC Marketing\SCS Marketing Services\System Support (soco Read Only Access)\Load Research\LR ART\main_style.css">';
put '</head>';
put '<body>';
put '<div id="container_fullmain">';
put ' <div id="header" align=center><strong>Edit Company</strong></div>';
put ' ';
put '<div align=Center>';
put " <a href='https://&trimmed_url./SASStoredProcess/do?_PROGRAM=/Stored_Processes/csg/Oneline_home'>Home</a></p>";
put '</div>';
put ' <div id="container_wide">';
put ' <form name="oneline" method=put ACTION="/SASStoredProcess/do">' ;
put '<table>';
put ' <tr> ';
put ' <th>Name of company:</th> ';
put ' <td> ';
put " <INPUT TYPE='Text' NAME='CompanyName' width='50' value=""" "&EscapedValue.""" maxlength='100'><br>";
put " <INPUT TYPE='hidden' NAME='id' width= '50' value=""&id"" maxlength='100'><br>";
put ' </td> ';
put ' </tr>';
put ' <tr> ';
put ' <th></th> ';
put ' <td> ';
put ' <input type="hidden" name="_program" value="/Stored_Processes/csg/Edit_Company_Action">';
put ' <input type="submit" value="Edit Company" class="goButton_long">';
put ' </td> ';
put ' </tr>';
put ' ' ;
put ' ' ;
put ' <br>';
put ' <center></center>';
put ' </FORM>';
put ' </div>';
put ' </div>';
put '</div> ';
put '</body> ';
put '</html> ';
run;

Re: issues parsing a single quote

Tom — Tue, 07 Nov 2023 14:39:32 GMT

Why mess around with the strings as macro variables if you are going to run a DATA step anyway.

You seem to be writing values inside of single quotes for the other HTML tags. So use QUOTE() to quote your CompanyName with single quotes.

data _null_;
  set National.Company;
  where id = &id;
  file _webout;
...
  length quoted_name $300;
  quoted_name = quote(trim(CompanyName),"'");
put " <INPUT TYPE='Text' NAME='CompanyName' width='50' value=" 
   quoted_name " maxlength='100'><br>";
...

So if companyname is "Bob's Burgers" then that should write:

value='Bob''s Burgers'

into the HTML file.

If that does not work for HTML then try something else. Perhaps using HTMLENCODE() so perhaps you write it as:

value='Bob&amp;s Burgers'

Instead. That might work better as a web form.

Re: issues parsing a single quote

RustyOwens — Tue, 07 Nov 2023 14:42:30 GMT

Forgive my inexperience and lack of experience in SAS. What I want is if the company name in the query is Bob's Burgers then I need Bob's Burgers to populate in the textbox. Will this do that?

Re: issues parsing a single quote

Tom — Tue, 07 Nov 2023 15:44:55 GMT

HTML files are TEXT files.

So try making an example one yourself and then edit it with a text editor and see what combinations of characters work. I would try each of these version (leave the rest of the HTML file unchanged).

value="Bob's Burger"
value='Bob''s Burger'
value='Bob&apos;s Burger'
value="Bob&apos;s Burger"

So those are in order. quoted with double quotes. quoted with single quotes with embedded single quotes doubled. Single quote replaced by HTML command for an apostrophe and quoted with single quotes. Same but quoted by double quotes.

Once you know which string works for HTML then you can work on how to get SAS to generate that string.

Again in order you can make those strings like this :

  quoted_company1=quote(trim(company_name));
  quoted_company2=quote(trim(company_name),"'");
  quoted_company3=quote(tranwrd(trim(company_name),"'",'&apos;'),"'");
  quoted_company4=quote(tranwrd(trim(company_name),"'",'&apos;'));

Let's try it out.

filename test 'c:\downloads\apos_test.html';
data test;
  file test;
  put '<html><body><pre>';
  company_name="Bob's Burgers";
  length quoted_company1 - quoted_company4 $50;
  quoted_company1=quote(trim(company_name));
  quoted_company2=quote(trim(company_name),"'");
  quoted_company3=quote(tranwrd(trim(company_name),"'",'&apos;'),"'");
  quoted_company4=quote(tranwrd(trim(company_name),"'",'&apos;'));
  put (quoted:) (=/);
  put '</pre></body></html>';
run;

So at least for just printing the text into a normal HTML file all but the second method works fine.

If you know the values have single quotes in them but do not have double qutoes then just use the first method. That is easy to produce in a data step since you can use the $QUOTE format to print the variable.

put ..... 'value=' company_name :$quote. .... ;

If the values might have double quote characters in them then I would use the third method since the resulting string as single quotes around it so that if you accidentally use it in a place where the SAS macro processor will look at it then the ' HTML command will not be mistaken for a reference to a macro variable named APOS.

Re: issues parsing a single quote

Patrick — Tue, 07 Nov 2023 22:48:09 GMT

SAS function htmlencode() lets you encode such characters.

Below how this could work for single and double quotes and the ampersand with minimal change to your existing code.

data have;
  Companyname="Bob's Burger";
run;

proc sql noprint;
  select htmlencode(Companyname,'apos quot amp') into :CompanyName
  from work.have
  /*where id = &id*/
  ;
quit;

%put CompanyName: %nrbquote(&CompanyName);