Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 172
Accepted Solution

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

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>

 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,616

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,616

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 172

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

Posted in reply to KurtBremser

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

 

Solution
3 weeks ago
Super User
Posts: 10,616

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 172

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

Posted in reply to KurtBremser

Hello Kurtz,

Thanks a lot for your help.

 

 

Regular Contributor
Posts: 172

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

Posted in reply to KurtBremser

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.

 

Super User
Posts: 10,616

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

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)
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 172

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

Posted in reply to KurtBremser

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;

 

 

 

Regular Contributor
Posts: 172

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

Posted in reply to KurtBremser

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.

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 133 views
  • 0 likes
  • 2 in conversation