topic Re: Creating XML Output Files. in SAS Programming

Creating XML Output Files.

keds — Wed, 27 Aug 2014 17:40:43 GMT

I want to take dataset as source and create a XML file as output which can be fed to downstream applicaitons.

My requirement is I want two different tags on same level from the dataset as source . i want to generate the XML file with nested tag upto level5.

if it can done using xmlmapper or proc template can somebody guide me , very new to this .

I want -<DestinationGuid> and -<Transactions> on same level.

e.g.

-<IMPORT>

-<DestinationGuid>

some value

-</DestinationGuid>

-<Transactions>

-<Transaction>

<NAME>Thrasher</NAME>

<CONFERENCE>Eastern</CONFERENCE>

<DIVISION>Southeas</DIVISION>

</Transaction>

-</Transactions>

-</IMPORT>

2) want to display tags on condition. E.g Suppose we have customer data and want to display in ouput file under 3 tags <Insert>, <update> or <delete>

depending on criteria like , If new customer then under insert,if existing then under update and if for deactivation then delete.

can above two things can be done ?

Thanks

Keds

Re: Creating XML Output Files.

keds — Thu, 28 Aug 2014 01:30:49 GMT

@Cynthia@sas any thoughts on this ?

Re: Creating XML Output Files.

Cynthia_sas — Thu, 28 Aug 2014 04:59:20 GMT

Hi:

By default, you can read about the type of XML that the XML Libname engine creates. There is 1 XML processing instruction, one root tag, then every row in the data gets 1 enclosing row tag and every column gets a tag and is nested inside the row tag. You can figure this out yourself by running a simple program to sent 3 rows of SASHELP.CLASS to an XML file as output. Then look at the XML file with Notepad and you will see the default structure. It sounds like the default structure is not what you want. In this case, you either have an XML Schema that defines the structure or you have a working model that you can design to.

Basically, I think these are your choices:

1) use the XML Libname Engine and an XML map file to create XML that conforms to the schema for export or

2) use the XML Mapper tool to map the working model to a SAS dataset (as if you were doing an import) and then you will understand what your SAS dataset has to look like to go the other way (from SAS to XML) using the same Map file. or

3) create some basic kind of XML with SAS and then use an XSL transform using some 3rd party tool to transform from one XML structure to another

4) write your own DATA step program to create the XML output file or

5) write your own tagset template for use with the XML libname engine to write the structure you want

Each method has an up side and a down side. The description of your XML is not enough information to go by to really do any constructive suggestions. The little snippet you show will definitely NOT be able to be produced with the XML Libname engine "out of the box" -- so you will have to do something. This paper might help you, http://www.lexjansen.com/pharmasug/2006/technicaltechniques/tt24.pdf but I don't actually recommend writing your own tagset template at this point in time. When that paper was written, the active version of SAS was 9.1.3 and the use of the XML map for Export was introduced in 9.2 and now, the XML Libname engine has other capabilities that need to be explored.

My recommendation would be that you start with the documentation on the SAS XML libname engine and reading about .map files tht you can use with the libname engine to make your XML file. Or to get you close enough to your structure so that you might just be able to modify the XML with a program to get the additional structure you want.

cynthia

Re: Creating XML Output Files.

keds — Thu, 28 Aug 2014 08:14:12 GMT

thanks Cynthia@sas.

to be very precise , i have a dataset Customer , with data as

CUST_ID Name GENDER Age Income status
137000 BBCD Female 20 32,340 married

i need to create a xml file as below:. can you help me in creating XMLMAPPER for this , i tried but i am stuck with

1) TABLE-PATH syntax="XPath"> option which tells the element whose value needs to be created ?

2) when i try to create a xmlmapper with automap= option , it is creating two different tables.

i am confused .

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

<Customer_data>
      <cust_info>
           <cust_id>137000</cust_id>
           <name>BBCD</name>
     </cust_info>
     <demographics>
           <Gender>Female</Gender>
            <Age>20</Age>
           <Income>32,340</Income>
           <Status>married</Status>
    </demographics>
</Customer_data>

Thanks

Keds

Re: Creating XML Output Files.

RW9 — Thu, 28 Aug 2014 09:01:46 GMT

Hi,

You can write it yourself pretty easily (I know oldschool):

data have;
attrib cust_id name gender age income status format=$20.;
cust_id="137000"; name="BBCD"; gender="Female"; age="20"; income="32,340"; status="married"; output;
run;

data _null_;
file "s:\temp\rob\myxml.xml";
set have;
if _n_=1 then put '<?xml version="1.0" encoding="windows-1252" ?>';
put '<Customer_data>';
put ' <cust_info>';
put '    <cust_id>' cust_id '</cust_id>';
put '    <name>' name '</name>';
put ' </cust_info>';
put ' <demographics>';
put '    <Gender>' gender '</Gender>';
put '    <Age>' age '</Age>';
put '    <Income>' income '</Income>';
put '    <Status>' status '</Status>';
put ' </demographics>';
put '</Customer_data>';
run;

Re: Creating XML Output Files.

keds — Thu, 28 Aug 2014 09:16:26 GMT

Thanks @RW9 .

But i want to create it using either XMLMapper or Proc templated , i dont know why but my user has told not to use put statement as it become more clumsy and less maintable .

can we do this by xmlmapper or proc template ?

Thanks

keds

Re: Creating XML Output Files.

RW9 — Thu, 28 Aug 2014 09:22:20 GMT

Well, XMLMapper I have only briefly looked at before discarding. You could re-write the above datastep as a template (right click on Results in SAS and choose templates, then navigate to Tagsets and select one). Up to you really. If the data is complicated or could change then there may be some benefit in creating a tagset. At the end of the day though, all a tagset is a condition set of put statements much like I gave you above.

Re: Creating XML Output Files.

keds — Thu, 28 Aug 2014 09:47:15 GMT

Thanks RW9, i am very much new to both XML , proc template . can you explain in a bit more details the procedure . yes as you said the data is more complicated and is going to change so it fits in.

can you let me know how to go about this are you telling data _null_ should be converted to dataset , i did that but right click on Results in SAS not able to see " choose Template" option ?

Once the tagsets are created would also like to know how to use it

Thanks

Keds

Re: Creating XML Output Files.

RW9 — Thu, 28 Aug 2014 09:58:05 GMT

Here is an introduction to the creation of tagsets: http://www2.sas.com/proceedings/sugi27/p178-27.pdf

They are pretty straightforward, try using the ExcelXp one for instance:

ods tagsets.excelxp file="...";

proc print data=sashelp.class;

run;

ods tagsets.excelxp close;

Oh, to add, what I mean by results is look to the window to the left of the code window, it will either say Explorer or Results, tab at bottom. Right click on the word Results at the top of the tree there and go down and you will Open, Explorer etc. and an option Templates. Click on templates and it will ope a browser window where you can navigate through templates/styles and see underlying code.

Re: Creating XML Output Files.

jakarman — Thu, 28 Aug 2014 11:02:15 GMT

keds you should take a step back a moment and review the requirements.
You are saying "I don't know why but my user has told not to use put statement". When that is because he has the expectation xml is generic standard and nothing on some layout and structuring exists that is misperception.

Only some simple structures lay-outs that are commonly used are working that way.

Does me remind of https://communities.sas.com/message/225244 stats can sdm-xl files in a xml format. They are hardly to be processed by a xml interface lacking the needed interface description.

A nice SAS document is: http://support.sas.com/documentation/cdl/en/engxml/64990/PDF/default/engxml.pdf

It documents the mapfile structure - SXLEMAP - that should give a structure to XML formatting.

You are lucky when the XMLfile should be an Oracle-bi or MS-access types. these are options aside the mapfile approach
The plain XML approach is a simple strcuture

Re: Creating XML Output Files.

Cynthia_sas — Thu, 28 Aug 2014 18:04:35 GMT

Hi:

The explantion of how to write a custom TAGSET template for use with the SAS XML Libname Engine is too long a discussion to go into here -- you really have to read the documentation and search for user group papers on the topic. Consider the attached screenshot of the output produced by the program below. As you can see, it shows an extra set of tags around OBSNO, NAME and SEX variables from SASHELP.CLASS. Then is shows another extra set of tags around AGE, HEIGHT and WEIGHT. I have structured my data quite rigidly -- this way, I know that OBSNO comes first, followed by NAME, then SEX, then AGE, HEIGHT and WEIGHT. So after the XML Libname engine writes the row delimiter tag, but before it writes the OBSNO info, I want it to write the opening CUST_INFO tag. So I made an event to write that tag. If OBSNO is not the first variable, then the tag will be written in the wrong place. Then, my template writes the other variables, but AFTER the SEX variable, the ending CUST_INFO tag is written. Then, BEFORE AGE, the DEMOGRAPHICS tag is written and AFTER WEIGHT, the DEMOGRAPHICS ending tag is written.

Based on what you said, this PROC TEMPLATE method is ONE way to do what you want, assuming that all you are doing is adding extra tags at specific places around specific variables. I actually don't recommend the TAGSET template approach anymore. Learning the template language is quite a task and really, it is only a different mechanism for PUT statements. So, according to your customer's rules, the template method is probably not going to be acceptable either. It probably is better to work with a schema and the XML Mapper.

Probably, if you have a schema (and you should have a schema for an unusual format such as you showed), you really need to try the XML Mapper approach. You'd have to get help from Tech Support for this approach (and honestly, you will probably need their help for the template approach too).

Cynthia

ods path work.temps(update)
sasuser.templat(update)
sashelp.tmplmst(read);

proc template;
   define tagset Tagsets.extra_tags;
      notes "SAS-XML generic XML-Data";
      define event SASTable;
         start:
            put "<ROOT_TAG>" NL;
            ndent;
            break;
         finish:
            xdent;
            put "</ROOT_TAG>" NL;
            break;
      end;
      define event SASRow;
         start:
            put "<";
            put NAME;
            put ">";
            put NL;
            ndent;
            break;
         finish:
            xdent;
            put "</";
            put NAME;
            put ">";
            put NL;
            break;
      end;
   define event ci_tag;
      start:
         put '<cust_info>' CR ;
         break;

      finish:
   put "</cust_info>" NL;
         break;
   end;
   define event dm_tag;
      start:
         put '<demographics>' CR ;
         break;

      finish:
   put "</demographics>" NL;
         break;
   end;

   define event SASColumn;
         start:
            trigger ci_tag start/ if cmp(upcase(NAME),'OBSNO');
            trigger dm_tag start/ if cmp(upcase(NAME),'AGE');
            ndent;
            put "<";
            put TEXT / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            put " name=""" / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            put NAME;
            put """" / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            break;
         finish:
            xdent / if exist( MISSING );
            break / if exist( MISSING );
            put " />" / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            put NL / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            xdent / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            break / if cmp( XMLDATAFORM , "ATTRIBUTE" );
            put "</";
            put NAME;
            put ">";
            put NL;
            xdent;
   trigger ci_tag finish / if cmp(upcase(NAME),'SEX');
   trigger dm_tag finish / if cmp(upcase(NAME),'WEIGHT');
            break;
      end;
    parent = tagsets.sasioXML;
   end;
run;

libname change xml 'c:\temp\Extra_Tags.txt'
              tagset=tagsets.extra_tags;
data change.customer_data;
** need to ensure that variables are in a certain order;
** use length statement;
** always write out <cust_info> before the OBSNO variable;
** always write </cust_info> AFTER the SEX variable;
** always write out the <demographics> tag before the AGE variable;
** always write out the </demographics> tag AFTER the WEIGHT variable;
length obsno 8 name $10 sex $1 age height weight 8;
     length ObsNo 8;
     set sashelp.class;
ObsNo = _n_;
if _n_ le 3 then output;
run;
libname change clear;