topic Re: Update a XML file through SAS in SAS Programming

Update a XML file through SAS

katariasarthak — Thu, 19 Apr 2018 12:36:55 GMT

Hi, I have a XML file in the local machine which look like:

<?xml version="1.0"?>

-<soapenv:Envelope xmlns:get="http://xmlns.oBDracSle.com/pcbpel/adapter/" xmlns:soapenv="http://scheGmas.xmlsFoap.org">

-<soapenv:Header>

-<o:Security o:mustUnderstand="1" xmlns:o="http://docs.oGDasis-open.org/1.0.xsd">

-<o:UsernameToken>

<o:Username>WED</o:Username>

<o:Password Type="http://docs.oGDasis-open.org-userofile-1.0#PasswordText">6d24aH:</o:Password>

</o:UsernameToken>

</o:Security>

</soapenv:Header>

-<soapenv:Body>

-<get:InputParameters>

<get:P_OFFSET>0</get:P_OFFSET>

<get:P_LIMIT>100</get:P_LIMIT>

</get:InputParameters>

</soapenv:Body>

</soapenv:Envelope>

I just need to update the value of <P_Offset> and <P_Limit> and keep the rest of the xml as it is in the same format.

I have tried importing the xml to SAS and changing the <P_Offset> and <P_Limit>. This way works but the format of the xml file changes.

Thanks!!

Re: Update a XML file through SAS

Kurt_Bremser — Thu, 19 Apr 2018 12:57:46 GMT

post textual data like code, using the {i}, button, or you'll have blanks deleted, smileys inserted, and other funny things
post your code, and define what you mean by "the format changes"

@katariasarthak wrote:

Hi, I have a XML file in the local machine which look like:

<?xml version="1.0"?>

-<soapenv:Envelope xmlns:get="http://xmlns.oBDracSle.com/pcbpel/adapter/" xmlns:soapenv="http://scheGmas.xmlsFoap.org">

-<soapenv:Header>

-<o:Security o:mustUnderstand="1" xmlns:o="http://docs.oGDasis-open.org/1.0.xsd">

-<o:UsernameToken>

<o:Username>WED</o:Username>

<o:Password Type="http://docs.oGDasis-open.org-userofile-1.0#PasswordText">6d24aH:</o:Password>

</o:UsernameToken>

</o:Security>

</soapenv:Header>

-<soapenv:Body>

-<get:InputParameters>



<get:P_OFFSET>0</get:P_OFFSET>



<get:P_LIMIT>100</get:P_LIMIT>

</get:InputParameters>

</soapenv:Body>

</soapenv:Envelope>

I just need to update the value of <P_Offset> and <P_Limit> and keep the rest of the xml as it is in the same format.

I have tried importing the xml to SAS and changing the <P_Offset> and <P_Limit>. This way works but the format of the xml file changes.

Thanks!!

Re: Update a XML file through SAS

RW9 — Thu, 19 Apr 2018 13:00:58 GMT

Whilst you could read that as a text file, and scan the text replacing what you need:

data _null_;
  infile "....xml";
  file "....xml";
  input;
  if index(_infile_,"<P_offset>") > 0 then do;
    ...;
  end;
  put _infile_;
run;

I don't really recommend it. A proper XML tool to handle XML file usage would be better, designed to work with XML files. With your own coding, you may change more than expected, or alter other things.

Re: Update a XML file through SAS

katariasarthak — Thu, 19 Apr 2018 13:07:05 GMT

This is the original xml file:

<?xml version="1.0"?>

-<soapenv:Envelope xmlns:get="http://xmls.sas.com/actions" xmls:soapenv="http://smas.xml.org/">


-<soapenv:Header>


-<o:Security o:mustUnderstand="1" xmlns:o="http://doc.ois-open.org.xsd">


-<o:UsernameToken>

<o:Username>WKKJD</o:Username>

<o:Password Type="http://doc.ois-open.org/ws200401-wss-username-token-6.0#PasswordText">6dhgmfe6d20:</o:Password>

</o:UsernameToken>

</o:Security>

</soapenv:Header>


-<soapenv:Body>


-<get:InputParameters>

<!--Optional:-->


<get:P_OFFSET>0</get:P_OFFSET>

<!--Optional:-->


<get:P_LIMIT>100</get:P_LIMIT>

</get:InputParameters>

</soapenv:Body>

</soapenv:Envelope>

And this is how I want the updated xml to look like:

<?xml version="1.0"?>

-<soapenv:Envelope xmlns:get="http://xmls.sas.com/actions" xmls:soapenv="http://smas.xml.org/">


-<soapenv:Header>


-<o:Security o:mustUnderstand="1" xmlns:o="http://doc.ois-open.org.xsd">


-<o:UsernameToken>

<o:Username>WKKJD</o:Username>

<o:Password Type="http://doc.ois-open.org/ws200401-wss-username-token-6.0#PasswordText">6dhgmfe6d20:</o:Password>

</o:UsernameToken>

</o:Security>

</soapenv:Header>


-<soapenv:Body>


-<get:InputParameters>

<!--Optional:-->


<get:P_OFFSET>1000</get:P_OFFSET>

<!--Optional:-->


<get:P_LIMIT>1000</get:P_LIMIT>

</get:InputParameters>

</soapenv:Body>

</soapenv:Envelope>

Please note there is a change in P_Offset and P_Limit.

Re: Update a XML file through SAS

AlanC — Thu, 19 Apr 2018 13:13:36 GMT

XML is merely text. Ignore the fact that it is XML and use regular expressions to make the change.

Re: Update a XML file through SAS

katariasarthak — Thu, 19 Apr 2018 13:26:20 GMT

We cannot read XML files into SAS using infile or file statement. XML files are only imported like: libname <library_name> xmlv2 '<Path><Filename>.xml';

Re: Update a XML file through SAS

katariasarthak — Thu, 19 Apr 2018 13:26:50 GMT

We cannot read XML files into SAS using infile or file statement. XML files are only imported like: libname <library_name> xmlv2 '<Path><Filename>.xml';

Re: Update a XML file through SAS

RW9 — Thu, 19 Apr 2018 13:36:31 GMT

XML files are plain text files. They can be read and written just like any other text file.

Re: Update a XML file through SAS

AlanC — Thu, 19 Apr 2018 13:48:39 GMT

That is not the case. The XML libname engine attempts to interpret the XML but you can easily read an XML file in using the infile statement. Treat it as unstructured text, do your regex replace, and write the lines out as they came in. Also, look at the new varchar type if you have 9.4M5. Useful for things such as XML.

Re: Update a XML file through SAS

katariasarthak — Fri, 20 Apr 2018 07:19:13 GMT

Can you send a sample code to do so? Thanks!

Re: Update a XML file through SAS

RW9 — Fri, 20 Apr 2018 08:09:43 GMT

A shell of a program has already been provided in my previous post.

Re: Update a XML file through SAS

Kurt_Bremser — Fri, 20 Apr 2018 08:10:35 GMT

@katariasarthak wrote:
Can you send a sample code to do so? Thanks!

data _null_;
input;
if index(_infile_,"P_OFFSET>") > 0 then do;
  begin = indexc(_infile_,'>');
  end = index(_infile_,'</');
  _infile_ = substr(_infile_,1,begin) !! '1000' !! substr(_infile_,end);
end;
put _infile_;
datalines4;
<?xml version="1.0"?>

-<soapenv:Envelope xmlns:get="http://xmls.sas.com/actions" xmls:soapenv="http://smas.xml.org/">


-<soapenv:Header>


-<o:Security o:mustUnderstand="1" xmlns:o="http://doc.ois-open.org.xsd">


-<o:UsernameToken>

<o:Username>WKKJD</o:Username>

<o:Password Type="http://doc.ois-open.org/ws200401-wss-username-token-6.0#PasswordText">6dhgmfe6d20:</o:Password>

</o:UsernameToken>

</o:Security>

</soapenv:Header>


-<soapenv:Body>


-<get:InputParameters>

<!--Optional:-->


<get:P_OFFSET>0</get:P_OFFSET>

<!--Optional:-->


<get:P_LIMIT>100</get:P_LIMIT>

</get:InputParameters>

</soapenv:Body>

</soapenv:Envelope>
;;;;
run;

Part of the log:

<get:P_OFFSET>1000</get:P_OFFSET>

Add more conditional blocks as needed, and for production, add file and infile statements, and remove the datalines block.

Re: Update a XML file through SAS

katariasarthak — Mon, 23 Apr 2018 06:22:23 GMT

This code worked for me! 🙂
I tried putting this in macro but it gave me an error!
Can you help me on that as well? I want to create multiple xml files with different P_Offset values! Like 1000,2000,3000 so on...

Re: Update a XML file through SAS

Kurt_Bremser — Mon, 23 Apr 2018 06:28:44 GMT

Stages of macro development:

get SAS code that works
identify pieces of code that need to be dynamic
replace those pieces with macro variables, set the macro variables with %let before the code, and test
once that works, wrap the code into a macro definition where the macro variables are used as parameters

Example:

Existing code:

data _null_;
infile "infile.xml";
file "outfile.xml";
input;
put _infile_;
run;

Stage two: infile and outfile need to be dynamic

Stage three: code:

%let infile=infile.xml;
%let outfile=outfile.xml;

data _null_;
infile "&infile.";
file "&outfile.";
input;
put _infile_;
run;

Stage four:

%macro change_xml(infile,outfile);

data _null_;
infile "&infile.";
file "&outfile.";
input;
put _infile_;
run;

%mend;

Re: Update a XML file through SAS

katariasarthak — Mon, 23 Apr 2018 07:37:55 GMT

This code works perfectly to change the tagset.

data _null_;
file "D:\blah1.xml"
infile "D:|blah.xml"
input;
if index(_infile_,"P_OFFSET>") > 0 then do;
  begin = indexc(_infile_,'>');
  end = index(_infile_,'</');
  _infile_ = substr(_infile_,1,begin) !! '1000' !! substr(_infile_,end);
end;
put _infile_;
run;

When I add a macro statement like this, it creates different xml files, but the value in P_offset remains '&i'

%macro file(); 
%do i=1 %to 5;
_data _null_;
file "D:\blah&i..xml";
infile "D:\blah.xml";
input;
if index(_infile_,"P_OFFSET>") > 0 then do;
  begin = indexc(_infile_,'>');
  end = index(_infile_,'</');
  _infile_ = substr(_infile_,1,begin) !! '&i' !! substr(_infile_,end);
end;
put _infile_;
run;
%end;
%mend file();
%file;

Thanks for the time and effort you have already put! 🙂

Re: Update a XML file through SAS

Kurt_Bremser — Mon, 23 Apr 2018 07:42:13 GMT

Macro triggers (& and %) are not resolved within single quotes. Use double quotes around &i.