Help using Base SAS procedures

sas proc sql pass through xml

Posts: 51

sas proc sql pass through xml


I am using proc sql pass through to extract xml data from a db2 server, the query looks like following:

select  xmlquery('data($DATA/*:annotation/*:EventStart/@data)') as MyData

from db2server

The returned data is like following:

<?xml version="1.0" encoding="windows-1252" ?>1234567

But all I need is the numeric part, i.e., 1234567. How to remove the leading <?xml version="1.0" encoding="windows-1252" ?>?


Super Contributor
Posts: 418

Re: sas proc sql pass through xml

You could use the TRANSTRN function to simply remove your code.

Aka something like

newstring=strip(Transtrn(oldvar,'<?xml version="1.0" encoding="windows-1252" ?>',''))

Of if you cannot guarantee that hte first section will always have this exact wording, you could simply substring the results starting at the first end position of >.

Aka something like


Note I didn't test either of these but in theory should work.

Posts: 51

Re: sas proc sql pass through xml

Posted in reply to Anotherdream

Thanks for reply. I am sure your method would work out, but I feel there should be a more xml-like way to do the job.

Respected Advisor
Posts: 4,736

Re: sas proc sql pass through xml

The more XML like way would need to happen as part of your XMLQuery expression. Once you get the result back it's just a string in a variable and you will need some string operation to get what you want. For the example string you've posted a simple RegEx should do though.

proc sql;

  create table test as

  select MyData, prxchange('s/<[^>]+>//oi',-1,MyData) as MyData_TagRemoved


      /* and here your pass-through SQL block */


        select '<?xml version="1.0" encoding="windows-1252" ?>1234567' as MyData

        from sashelp.class(obs=1)




Regular Expressions are also implemented in DB2 IBM Knowledge Center. If you can't set-up your XMLQuery in a way that you only get back what you need then I'd probably would try and implement the string parsing/replacement within DB2 - so what I've done in above example with prxchange() - to not transfer unnecessary data from the Database to SAS.

Ask a Question
Discussion stats
  • 3 replies
  • 1 like
  • 3 in conversation