How to convert an XML file into a flat file using SAS EG 7.11 ?

[alepage]

Hello,

The conversion a data set into an xml file is made using the following code (ex: sashelp.class).  But how do we convert this xml file into a flat file like the one after the sas code.

libname newfile xml "C:\Test\XML\testtoday.xml"

xmltype=oracle;

data newfile.testtoday;

set sashelp.class;

run;

"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Alfred </Name><Sex> M </Sex><Age> 14 </Age><Height> 69 </Height><Weight> 112.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Alice </Name><Sex> F </Sex><Age> 13 </Age><Height> 56.5 </Height><Weight> 84 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Barbara </Name><Sex> F </Sex><Age> 13 </Age><Height> 65.3 </Height><Weight> 98 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Carol </Name><Sex> F </Sex><Age> 14 </Age><Height> 62.8 </Height><Weight> 102.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Henry </Name><Sex> M </Sex><Age> 14 </Age><Height> 63.5 </Height><Weight> 102.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> James </Name><Sex> M </Sex><Age> 12 </Age><Height> 57.3 </Height><Weight> 83 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Jane </Name><Sex> F </Sex><Age> 12 </Age><Height> 59.8 </Height><Weight> 84.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Janet </Name><Sex> F </Sex><Age> 15 </Age><Height> 62.5 </Height><Weight> 112.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Jeffrey </Name><Sex> M </Sex><Age> 13 </Age><Height> 62.5 </Height><Weight> 84 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> John </Name><Sex> M </Sex><Age> 12 </Age><Height> 59 </Height><Weight> 99.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Joyce </Name><Sex> F </Sex><Age> 11 </Age><Height> 51.3 </Height><Weight> 50.5 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Judy </Name><Sex> F </Sex><Age> 14 </Age><Height> 64.3 </Height><Weight> 90 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Louise </Name><Sex> F </Sex><Age> 12 </Age><Height> 56.3 </Height><Weight> 77 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Mary </Name><Sex> F </Sex><Age> 15 </Age><Height> 66.5 </Height><Weight> 112 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Philip </Name><Sex> M </Sex><Age> 16 </Age><Height> 72 </Height><Weight> 150 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Robert </Name><Sex> M </Sex><Age> 12 </Age><Height> 64.8 </Height><Weight> 128 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Ronald </Name><Sex> M </Sex><Age> 15 </Age><Height> 67 </Height><Weight> 133 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> Thomas </Name><Sex> M </Sex><Age> 11 </Age><Height> 57.5 </Height><Weight> 85 </Weight></ROW>|</ROWSET>
"<?xml version=""1.0"" encoding=""WINDOWS-1252""?><ROWSET>"|<ROW><Name> William </Name><Sex> M </Sex><Age> 15 </Age><Height> 66.5 </Height><Weight> 112 </Weight></ROW>|</ROWSET>

[Kurt_Bremser]

A XML file is a text file, just open it with notepad++ or similar.

[alepage]

Hello Kurt,

May be my question is not clear.  I apologize for that.

If I look at the XML FILE and if I open it with notepad++, What I will see is:

<?xml version="1.0" encoding="windows-1252" ?>
<ROWSET>
 <ROW>
  <Name> Alfred </Name>
  <Sex> M </Sex>
  <Age> 14 </Age>
  <Height> 69 </Height>
  <Weight> 112.5 </Weight>
 </ROW>...

And what I would like to do is to put all the information for an observation on the same row like below:

<ROWSET><ROW><Name> Alfred </Name>...<Height> 69 </Height><Weight> 112.5 </Weight></ROW></ROWSET>

...next observations

[Kurt_Bremser · Accepted solution]

Try this:

libname newfile xml "$HOME/sascommunity/testtoday.xml" xmltype=oracle;

data newfile.testtoday;
set sashelp.class;
run;

filename in "$HOME/sascommunity/testtoday.xml";
filename out "$HOME/sascommunity/testtoday2.xml";

data _null_;
infile in truncover;
file out lrecl=1000;
length line $1000;
retain flag 0 line;
input inline $100.;
if not flag
then do;
  if index(inline,'<ROW>')
  then do;
    line = strip(inline);
    flag = 1;
  end;
  else put inline; * keep irrelevant lines as-is;
end;
else do;
  line = strip(line) !! strip(inline);
  if index(inline,'</ROW>')
  then do;
    put line;
    flag = 0;
    line = '';
  end;
end;
run;
  

Note that you don't want repeated ROWSET tags, as these would break the XML structure.

[alepage]

Hello Kurtz,

Thanks a lot for your help.

[alepage]

Hello Kurtz,

I have test and modify your code to adjust it to my needs.  It works well.  Now if I want to use your code into a macro function to pass the File location path and the file name as well.  It doesn't seems to works.

I have put % sign to if then do else and index but I think I have difficulties with the expression: if not flag...

How do we modify that for a macro function.

Thanks in advance for your help.

[Kurt_Bremser]

If the only thing that needs to be made dynamic are the dataset and file names, the rest of the code stays as it is.

eg

start with

proc print data=sashelp.class;
run;

make dataset dynamic:

%let dsname=sashelp.class;
proc print data=&dsname;
run;

wrap into macro definition:

%macro print(dsname);
proc print data=&dsname;
run;
%mend;
%print(sashelp.class)

[alepage]

Hello Kurt,

I would like to catch the xml prolog into a macro variable (ex:<?xml version="1.0" encoding="WINDOWS-1252"?>).  There after, I need to modify the special characters in the macro variable then copy the prolog (macro variable) at each line (that the format require by the customer).

It is why I am asking how to put the code into a macro function.

%let prolog1=<?xml version="1.0" encoding="windows-1252" ?>;

%let target='"';

%let replacement=%bquote(");

%let prolog1=%sysfunc(tranwrd(%quote(&prolog1.),&target.,&replacement.));

%put &prolog1;

[alepage]

Hello Kurt,

I have completed my test and with your help, I was able to convert an XML file into a Delimiter Separator Value file (*.dsv) with the expected format.

Thanks for your help which is very appreciated.