topic Re: Need to remove double quotes but leave the original ones already in data? in SAS Procedures

Need to remove double quotes but leave the original ones already in data?

LeeJenson — Mon, 18 Oct 2010 11:51:26 GMT

Hi,

I am using the following code to export data to remove the header from
the output file. My objective is to insert some XML into a little
table and export the data into a file (noheader).

proc sql;
create create table test1;
(xml char(500)
);
quit

proc sql;
insert into test1
set xml='';
quit;

filename exTemp temp;
proc export data=test2 outfile=exTemp dbms=csv;
run;

data _null_;
infile extemp firstobs=2;
file "c:\test.xml";
input;
put _infile_;
run;

This produces an output file like this

""

and I need the output to look like the original

Please could someone assist please.

PS: If I am going about this all the wrong way then please let me
know also.

Thank You

Lee

Re: Need to remove double quotes but leave the original ones already in data?

Cynthia_sas — Mon, 18 Oct 2010 14:57:10 GMT

I'm just curious why you're not using the XML libname engine or the DATA step to write what you want.

[pre]
data _null_;
file 'c:\temp\fakexml.xml';
put @1 '';
run;
[/pre]

If you look at the file, c:\temp\fakexml.xml with Notepad, you will see your one line without extra quotes and without any header. FYI, your initially posted SQL has several syntax issues that should be producing errors in your SAS log (2 CREATE keywords and semi-colon in the wrong place).

You say you want to insert some XML into a "little table" -- is that little table in CSV format??? How did you create the table? What does it look like? It is possible in a DATA step program to write a line only 1 time (such as at the top of a file) but you haven't provided much information to go on.

cynthia

Re: Need to remove double quotes but leave the original ones already in data?

LeeJenson — Mon, 18 Oct 2010 16:02:12 GMT

Thanks for the reply.

You say you want to insert some XML into a "little table" -- is that little table in CSV format???
Lee>The source table contained one variable and one record. The xml string is hardcoded apart from one bit,which is a variable.

How did you create the table?
Lee>I create the table in the code attached. Just one column, one record.

Your programmed worked
6912 data _null_;
6913 file "C:\temp\test.xml";
6914 put @1 'xyz';
6915 run;

NOTE: The file "C:\temp\test.xml" is:
File Name=C:\temp\test.xml,
RECFM=V,LRECL=256

NOTE: 1 record was written to the file "C:\temp\test.xml".

But when I change it to support variable input and change the outside quotes to double quotes it has a problem with this solution.

6916 %let test=abc;
6917 data _null_;
6918 file "C:\temp\test.xml";
6919 put @1 "&test.";
--------------------
49
ERROR: Invalid hexadecimal constant string ">

Re: Need to remove double quotes but leave the original ones already in data?

Cynthia_sas — Mon, 18 Oct 2010 16:22:52 GMT

Hi:
Even though you can write your XML as one long quoted string, as you discovered, double quotes inside double quotes lead to other issues in the SAS code. So, there's nothing wrong with writing part of the string in SINGLE quotes and then part of the string in DOUBLE quotes, so the macro variable resolves correctly and you do not get quoting issues in the compiler.

This program
[pre]
%let test=abc;
data _null_;
file 'c:\temp\fakexml.xml';
put @1 ''
"&test.";
run;
[/pre]

produces this outout in the c:\temp\fakexml.xml file:
[pre]
<test><testdo yyy="abc"><test1 xxx="x">abc</test1></testdo></test>
[/pre]

You could also use syntax in your DATA step program like this (I know, this isn't valid XML, but I was more interested in showing a couple of different quoting possibilities with the macro variable):
[pre]
. . . more code . . .
put '** Another example';
put @1 '<wombat>'
"&test"
'<test1 koala="' "&test&test" '">wombat</test1>';
[/pre]

to produce this output in the file created by the PUT statements:
[pre]
** Another example
<wombat>abc<test1 koala="abcabc">wombat</test1>
[/pre]

Log from the complete program is shown below.
cynthia

[pre]
88 %let test=abc;
89 data _null_;
90 file 'c:\temp\fakexml.xml';
91 put @1 '<test><testdo yyy="abc"><test1 xxx="x">'
92 "&test.</test1></testdo></test>";
93
94 put '** Another example';
95 put @1 '<wombat>'
96 "&test"
97 '<test1 koala="' "&test&test" '">wombat</test1>';
98 run;

NOTE: The file 'c:\temp\fakexml.xml' is:
Filename=c:\temp\fakexml.xml,
RECFM=V,LRECL=256,File Size (bytes)=0,
Last Modified=18Oct2010:10:13:41,
Create Time=18Oct2010:08:52:49

NOTE: 3 records were written to the file 'c:\temp\fakexml.xml'.
The minimum record length was 18.
The maximum record length was 66.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
[/pre]

Re: Need to remove double quotes but leave the original ones already in data?

LeeJenson — Mon, 18 Oct 2010 17:06:37 GMT

This was the perfect solution for me

%let test=abc;data _null_;
file 'c:\temp\fakexml.xml';
put @1 ''
"&test.";
run;

thank you 5 million times

Lee