08-27-2014 01:40 PM
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.
<Imports description="Teams of the National Hockey League">
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 ?
08-28-2014 12:59 AM
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.
08-28-2014 04:14 AM
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" ?>
08-28-2014 05:01 AM
You can write it yourself pretty easily (I know oldschool):
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;
if _n_=1 then put '<?xml version="1.0" encoding="windows-1252" ?>';
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>';
08-28-2014 05:16 AM
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 ?
08-28-2014 05:22 AM
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.
08-28-2014 05:47 AM
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
08-28-2014 05:58 AM
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;
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.
08-28-2014 07:02 AM
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
08-28-2014 02:04 PM
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).
ods path work.temps(update)
define tagset Tagsets.extra_tags;
notes "SAS-XML generic XML-Data";
define event SASTable;
put "<ROOT_TAG>" NL;
put "</ROOT_TAG>" NL;
define event SASRow;
define event ci_tag;
put '<cust_info>' CR ;
put "</cust_info>" NL;
define event dm_tag;
put '<demographics>' CR ;
put "</demographics>" NL;
define event SASColumn;
trigger ci_tag start/ if cmp(upcase(NAME),'OBSNO');
trigger dm_tag start/ if cmp(upcase(NAME),'AGE');
put TEXT / if cmp( XMLDATAFORM , "ATTRIBUTE" );
put " name=""" / if cmp( XMLDATAFORM , "ATTRIBUTE" );
put """" / if cmp( XMLDATAFORM , "ATTRIBUTE" );
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" );
trigger ci_tag finish / if cmp(upcase(NAME),'SEX');
trigger dm_tag finish / if cmp(upcase(NAME),'WEIGHT');
parent = tagsets.sasioXML;
libname change xml 'c:\temp\Extra_Tags.txt'
** 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;
ObsNo = _n_;
if _n_ le 3 then output;
libname change clear;