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

I want to insert the XML as a string. May refer to the SAS code.

Not sure if there are double quote within the string.

 

proc sql;
create table A (Requested_XML varchar(3000));

proc sql; insert into A values "<?xml version="1.0"?><catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth look at creating applications with XML.</description> </book>";
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,a datetime constant,a missing value, ), +, ',', -, MISSING, NULL, USER.
 
ERROR 79-322: Expecting a (.
 
ERROR 202-322: The option or parameter is not recognized and will be ignored.
 
ERROR: Bit Constants are not supported by SQL.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

To make a string literal in SAS you can use " or ' on the outside.  But if the character you want to use on the outside exists in the data you need to double it up.  

 

So if you string looks like:

<?xml version="1.0"?>

And you want to enclose it in " then you need to change it to:

<?xml version=""1.0""?>

Before adding the quotes.

"<?xml version=""1.0""?>"

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

To make a string literal in SAS you can use " or ' on the outside.  But if the character you want to use on the outside exists in the data you need to double it up.  

 

So if you string looks like:

<?xml version="1.0"?>

And you want to enclose it in " then you need to change it to:

<?xml version=""1.0""?>

Before adding the quotes.

"<?xml version=""1.0""?>"
ChrisWoo
Obsidian | Level 7
i see.. TQVM
ChrisWoo
Obsidian | Level 7

Hi Kurt,

 

Any other way that I can do it without putting additional single / double quotes on every strings that contain quotes like "Chris"?

Tom
Super User Tom
Super User

Leave the strings in data. 

 

For example read the string from a file:

 

data strings;
  input xml_string $200.;
datalines4;
<?xml version="1.0"?><catalog><book id="bk101"><author>Gambardella, Matthew</author><title>XML Developer's Guide</title><genre>Computer</genre><price>44.95</price><publish_date>2000-10-01</publish_date><description>An in-depth look at creating applications  with XML.</description></book>
;;;;

If you then want to insert that value into another dataset you can just use code instead of string literals.

 

proc sql;
insert into A (select xml_string from strings);

If you then need to use the data to generate code you can use the QUOTE() function.  For example you could put the quoted string into a macro variable.

 

data _null_;
  set strings;
  call symputx('mvar',quote(trim(xml_string),"'"));
run;
proc sql;
insert into A values (&mvar);

 

 

ballardw
Super User

Copy from the LOG the entire procedure with code and the error messages. On the forum open a text box and paste the entire log.

Typically the 22-322 error, and many others will place diagnostic characters into the log where the problem occurs so it helps to see that. Also the main message windows will reformat pasted text meaning that those diagnostic characters appear incorrectly if not pasted into a text box that preserves text format.

 

One strongly suspects it is related to multiple quotes, both single and double.

"<?xml version="1.0"?>

The second magenta " above would end the "value" treated as <?xml version=1

the 1. would then be expected to be another value but is not done correctly for insert.

Generally with SAS if you need to have double quote character in a value you could use a single quote around the value. But you also have a single quote in

XML Developer's Guide

Which will cause a similar issue. When you need quotes as part of a value then enter them twice. Also the Values expects a () around the value .

Try:

proc sql;
    create table A (Requested_XML varchar(3000));

proc sql;
	insert into A values
("<?xml version=""1.0""?><catalog>   <book id=""bk101"">      <author>Gambardella, Matthew</author>      <title>XML Developer''s Guide</title>      <genre>Computer</genre>      <price>44.95</price>      <publish_date>2000-10-01</publish_date>      <description>An in-depth look at creating applications  with XML.</description>   </book>");
quit;

Personally I would not use INSERT at all. A data step reading a text file is likely to be easier.

 

 

 

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!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1109 views
  • 0 likes
  • 4 in conversation