Help using Base SAS procedures

XML Layout file structure

Reply
N/A
Posts: 0

XML Layout file structure

Does SAS Support XML format layout , I am stuck in POC for our SAS EBI Reporting selling for which I have to populate the XML data as mentioned above


data1
data2


/* for your understanding (this is not i want in XML) - From Table B */
data1
data2
data3
/* End of Table B */

/* for your understanding (this is not i want in XML)- From Table C */

TXTLInE1
<------ I'll have 5000 lines of text here
TXTLInE5000

your earliest help could let us win bid..

Regards,
SAS Super FREQ
Posts: 8,740

Re: XML Layout file structure

Hi:
SAS supports XML (for both input XML to SAS; and output SAS to XML) in several ways:
1) SAS XML Libname Engine supports
-- XML to SAS dataset format
-- SAS dataset to XML table (can use Oracle format, CDISC format XBRL format, etc)
-- SAS dataset to custom XML table using Custom TAGSET templates

2) ODS supports
-- "standard" XML formats (such as Microsoft Spreadsheet Markup Language)
-- SAS internal XML format (created by ODS MARKUP TAGSET=DEFAULT)
-- Custom XML from SAS procedure output using custom TAGSET templates
-- SASReport XML -- which is used by the BI Platform clients, like WRS, EG, and other clients

I'm not exactly clear on why you need to create separate XML files for an EBI solution using the BI Platform, when the platform and all the client apps create SASReport XML by default.

To compare the Libname Engine XML with SASReport XML, run the program below and review each result file in Notepad or a text editor. You can always -customize- the type of XML Tags that get written by SAS by creating a CUSTOM tagset template for use with either the Libname engine or ODS -- however, remember that the client apps in the BI platform may only handle SASReport XML and not custom XML.

cynthia
[pre]
**1) What Libname Engine XML looks like;
libname xmlout xml 'c:\temp\classout.xml';

data xmlout.class;
set sashelp.class;
if _n_ le 3;
age_in_5 = age + 5;
run;

libname xmlout clear;

**2) What SASReport XML looks like;
ods tagsets.sasreport11 file='SAS_flavor.xml';

proc print data=sashelp.class(obs=5);
run;

ods tagsets.sasreport11 close;
[/pre]
N/A
Posts: 0

Re: XML Layout file structure

HI Cynthia,
Thanks a million for your reply,
I want to get this out as a XML file , I am getting the data from different tables and want to produce one file having the information as shown in example file.

I am using DI for the same , How can I achieve this.

Can you please help me with the direction or site where i can find the specific to my requirement.

Thanks once again.
SAS Super FREQ
Posts: 8,740

Re: XML Layout file structure

Hi:
If you were working in Base SAS, then, I'd recommend that you use the #1 example -- the SAS XML Libname Engine to create your XML file. Since the XML Libname Engine works with ONE dataset in and ONE XML file out, however, you might have to do some data manipulation ahead of time before the XML creation step.

I do not have a current copy of DI Studio to test with. But it was my understanding that the DI Studio documentation and classes did have some information on creating XML files. I thought they achieved this through DI Studio transforms.

I don't understand the XML samples you showed in the XML snippets above, you show some XML but then say it's NOT what you want? That's sort of confusing. It seems to me that you have some variables in TABLE A and some other, possibly related variables in TABLE B and you need to get those and possibly some information from TABLE C into ONE XML file????

Is what you show the kind of XML you want to get or are you trying to show that the final XML will be composed of some columns from Table A and some columns from Table B???

You might want to contact SAS Tech Support for more immediate help, especially if DI Studio is involved, because they can help you with the DI Studio piece of your question.

For information purposes, I'm showing how you can use information from 2 tables to make 1 XML file using the XML libname engine. If you wanted to generate different XML tags than what you get by default, then you'd have to write your own custom tagset template. Again, I believe that DI Studio has some built in mechanisms for dealing with XML -- especially CDISC related XML -- but you'd have to look at the DI Studio doc or work with Tech Support.

cynthia

In the example program below, I have 3 variables coming from TABLEA and 2 variables coming from TABLEB. I merge the 2 files together and rename the variables so that the new names are used in the XML file.
Program:
[pre]
data tableA;
infile datalines;
input char $ color $ about $;
return;
datalines;
bigbird yellow happy
kermit green happy
oscar green grouch
;
run;

data tableB;
length show1 show2 $30;
infile datalines dsd;
input char $ show1 $ show2 $;
return;
datalines;
bigbird,"Sesame Street","Big Bird in China"
kermit,"Sesame Street", "Muppet Show"
oscar,"Sesame Street","Follow That Bird"
;
run;


libname xmlout xml "c:\temp\both.xml";
data xmlout.muppet (rename=(char=column1 color=column2 about=column3
show1=column4 show2=column5));
merge tableA(in=ina) tableB(in=inb);
by char;
if ina and inb;
run;

libname xmlout clear;
[/pre]

XML output file:
[pre]
<?xml version="1.0" encoding="windows-1252" ?>
<TABLE>
<MUPPET>
<column1> bigbird </column1>
<column2> yellow </column2>
<column3> happy </column3>
<column4> Sesame Street </column4>
<column5> Big Bird in China </column5>
</MUPPET>
<MUPPET>
<column1> kermit </column1>
<column2> green </column2>
<column3> happy </column3>
<column4> Sesame Street </column4>
<column5> Muppet Show </column5>
</MUPPET>
<MUPPET>
<column1> oscar </column1>
<column2> green </column2>
<column3> grouch </column3>
<column4> Sesame Street </column4>
<column5> Follow That Bird </column5>
</MUPPET>
</TABLE>
[/pre]
N/A
Posts: 0

Re: XML Layout file structure

Let me explain in brief:

<-------------- I want to read the table name and make a tag
data1 <---------Read the column name and make a tag
data2 <--------same as column1


/* for your understanding (this is not i want in XML) - From Table B */
data1 <------ reading this info from different table a making tag of column1, column2, column3 (2,3 as below)
data2
data3
/* End of Table B */

/* for your understanding (this is not i want in XML)- From Table C */

TXTLInE1
<------ I'll have 5000 lines of text here
TXTLInE5000
N/A
Posts: 0

Re: XML Layout file structure

Thanks for your efforts and help....

Wishes,
Contributor
Posts: 61

Re: XML Layout file structure

Hi GR....

first u have to register xml files into DI Studio.

You can access XML tables through a SAS XML library, a library that uses the SAS XML LIBNAME engine to access an XML file.
The metadata for a SAS XML Library includes the following unique attributes

specifies the SAS XML engine to use when accessing XML files in this library. This field corresponds to the XML option on the LIBNAME statement.
XML File
specifies the XML document to import or export. You must specify the entire pathname and file extension. Corresponds to the XML-document-path option on the LIBNAME statement. For example, you could specify the pathname for a directory where XML files are stored, or an XML document that contains data and metadata for one or more tables.

specifies the XML format used by files in the library. Specify a blank value if you are accessing an XMLMap. This field corresponds to the XMLTYPE= option on the LIBNAME statement. Valid values include blank, GENERIC, ORACLE, CDISCODM, EXPORT, and MSACCESS.

specifies the type of access that users are allowed to the library. Valid values are blank, TEMP and READONLY. Corresponds to the ACCESS= option on the LIBNAME statement. See the Usage Note below.

displays the Advanced Options window for XML libraries. Enables you to specify XMLMaps, options for working with CDISC ODM elements, and other options. An XMLMap is used by the SAS XML LIBNAME engine in order to import an XML document as a SAS data set. If your XML document is not in the physical structure required by the XML engine, you can create an XMLMap to tell the XML engine how to interpret the XML markup in order to successfully import the document.

After assigning library....by using source desiner u can get the table as data sets

now u will get all the xml files into sas data set...run the jobs as u desired out to combine tables or splitting tables or what ever u required.....

after completing the staging process do the targets and EG or WEB Reports as xml formats......etc.....

cheers.. Message was edited by: venkatesh
SAS Super FREQ
Posts: 8,740

Re: XML Layout file structure

Hi:
The process you describe is for translating XML files INTO SAS data set format. So a somewhat reverse process could be used to transfer your SAS data sets INTO XML format. At least now, GR has some idea of the places to look in DI Studio. And, there's still Tech Support...
cynthia
Contributor
Posts: 61

Re: XML Layout file structure

Hi GR.,

now u have a SAS dataset......

In a SAS Data Integration Studio job, if you want to put data into an XML table, you must use an XML Writer transformation. You cannot use the Table Loader transformation to load an XML table.\

for example.

The XML Writer is a transformation that puts data into an XML table. The target of an XML Writer must be an XML table in a SAS XML Library. Access to the library must not be set to READONLY. (For more information about library access options, see Define Metadata for XML Libraries.)
When you add an XML table to a process flow and you make the XML table an output, an XML Writer is inserted before the XML table. For example, if you drag an XML table named C_TEMP from the Inventory tree, drop it into a job in the Process Editor, and then right-click the table and select Make Output from the pop-up menu, an XML Writer transformation is added just before the table,

To complete the process flow, you can drag a source table into the empty drop zone. If you then submit the job for execution, the XML Writer reads the data from the source table and loads it into the target (C_TEMP).

In many cases, you can accept the default metadata for a XML Writer transformation and then run the job. In other cases, you can right-click the transformation, select Properties from the pop-up menu and then update the metadata on a tab in the properties window. For example, you could click the Options tab and specify XML engine options for the LIBNAME statement that the XML Writer transformation generates.

For details about XML engine options, see the SAS XML LIBNAME Engine User' Guide.

For an example of how an XML writer transformation can be used in a job, see Data Flow for a Web Service Job.

I hope this will helps u......

Cheers
Venky..
N/A
Posts: 0

Re: XML Layout file structure

Thanks Venktesh and Cynthia,

I have not worked much in producing the XML frile from SAS. also I have very customized requirement.
Looking at the reply Post No. 1928 Cynthia has almost reolved my case.
but I was wondering what if I have to write the macthing tag outside as below:


bigbird
yellow
happy
Sesame Street

Big Bird in China

Secondly , Can I create the more than one sub-tag with in each tag as per my wish:


bigbird
any text
yellow
any text
happy
Sesame Street

Big Bird in China

When I do POST View all the lines comes in same starting position , please consider the XML Hierarchy.

Wishes,
SAS Super FREQ
Posts: 8,740

Re: XML Layout file structure

Hi:
Some forum hints...if you have a section of code or XML, where you want the indention to be respected, then put
squarebracket-pre-squarebracket and squarebracket-/pre-squarebracket around the code. As in
[pre]
#pre#
some code
indented code
more code
something else
the end
#/pre#
[/pre]

Except where I have the first #, you would use [ and where I have the second pre#, you would use pre] -- that's [ pre ] without any spaces before or after the pre.

Next, if you look carefully at my XML, you will see that my XML processing instruction is the <?xml version="1.0" encoding="windows-1252" ?>. Next, my enclosing ROOT tag is <TABLE> and </TABLE>. So the <MUPPET> tag encloses each SAS observation in a way that produces a well-formed XML file. If you needed the XML Libname Engine to write some information in a tag in a different way than it normally does (which is what I show), then you would have to alter the TAGSET template used by the XML Libname engine to produce the XML tags. What gets written out tag-wise and in what order is controlled by "events" in the tagset template and if you wanted to write tags out differently, you'd have to change the event handling in the tagset template.

I'm not sure that an XMLMap would help you, even in SAS 9.2, because you're producing XML that is non-standard, from the SAS standpoint -- SAS really wants for the end of the observation tag (in this case </MUPPET>) to be after the last var (column5) for every obs in the data set. If you need for different XML to be written For example, in the XML below, SAS would automatically write out the <ROOT> and <ROW> tags. If you wanted to write out the <TABLEA> and <TABLEB> tags, that also would be a tagset template change. You could write out the "subtext" tag either by making the text a variable value in the data set or by hardcoding the text to be written out as part of an event in the tagset template.
[pre]




bigbird
some text comment
yellow
happy
Sesame Street


Big Bird in China



[/pre]

If you look at some of the examples in this PharmaSUG paper, perhaps you'll get a better idea of how a tagset template can be customized to produce custom XML.
http://www.lexjansen.com/pharmasug/2006/technicaltechniques/tt24.pdf

cynthia
N/A
Posts: 0

Re: XML Layout file structure

Hi Cynthia,
I dont know if I am taking your time long time in this but... ofcourse all your help is taking me closure to my requirement,,,

I have a requirement like this:-
1) I want to insert the sub-tag as : (see in scenerio)
..........

2) Secondly, I just want to have the text lines not with in the tag:
as you mentioned:

-----> I want NO tage here
Big Bird in China --------> only these lines
I want NO tage here

------------------Real SCENRIO------------------------------------




808202
160531
ANY TEXT
605
SAB
7
BS
1

ABFGHIYZ123 1
123
ABCDEFGHIJKLMNOPQRSTUVXYZ123 1
ABCDEFGHIJKLMNOPQRSTUVXYZ123 2
ABCDEFGHIJKLMNOPQRSTUVXYZ123 3



-------------------------------------/ Real Scenerio------------------------------------

I am calling all the Address and text lines information based on the DLVRY_ID columns and also acting as foreign key , which is available in Address Table (as Table 2) and Merge_ENC table (as Table 3)

and pritinting all Address and Text Line info outside the without any tage as mentioned above.

above two I am finding as a challenge.

Thanks,

Wishes
SAS Super FREQ
Posts: 8,740

Re: XML Layout file structure

Hi:
As I explained earlier, the SAS XML Libname engine is ONE SAS FILE IN/ONE XML FILE OUT -- so if you have data from 2 SAS files or tables that will go into 1 XML file, then you have to bring those 2 tables together -- either with a merge or a join or a view. For example, if you have ALL your variables named the same as what you want your tags to be -- including the TxtLine variables, then you can get almost everything you want from the LIBNAME engine. For example, if you had your variables all structured, in ONE table, with the variable names the same as the tag names, as shown:
[pre]
data test;
data_supp_num = 808202;
dlvry_id = 150531;
dlvry_id2 = 'ANY TEXT';
dlvry_typ = 605;
sys_cd = 'SAB';
rec_typ = 7;
sys_id = 'BS';
ID = 1;
ADDRESS1 = 'ABFGHIYZ123 1';
ADDRESS2= '123';
TxtLine1= 'ABCDEFGHIJKLMNOPQRSTUVXYZ123 1 ';
TxtLine2= 'ABCDEFGHIJKLMNOPQRSTUVXYZ123 2 ';
TxtLine3= 'ABCDEFGHIJKLMNOPQRSTUVXYZ123 3';
run;

libname defout xml 'c:\temp\default_out.xml';
data defout.test;
set work.test;
run;
libname defout clear;
[/pre]

Again, note how all the variable names are the same as the tag names that you said you wanted. Note how all the information is in ONE data set.

So, the above code produces this XML. All you need to do now, is create a custom tagset template to insert the <CUST_INFO> and </CUST_INFO> tags. (More on this below)
[pre]

<?xml version="1.0" encoding="windows-1252" ?>
<TABLE>
<TEST>
<data_supp_num> 808202 </data_supp_num>
<dlvry_id> 150531 </dlvry_id>
<dlvry_id2> ANY TEXT </dlvry_id2>
<dlvry_typ> 605 </dlvry_typ>
<sys_cd> SAB </sys_cd>
<rec_typ> 7 </rec_typ>
<sys_id> BS </sys_id>
<ID> 1 </ID>
<ADDRESS1> ABFGHIYZ123 1 </ADDRESS1>
<ADDRESS2> 123 </ADDRESS2>
<TxtLine1> ABCDEFGHIJKLMNOPQRSTUVXYZ123 1 </TxtLine1>
<TxtLine2> ABCDEFGHIJKLMNOPQRSTUVXYZ123 2 </TxtLine2>
<TxtLine3> ABCDEFGHIJKLMNOPQRSTUVXYZ123 3 </TxtLine3>
</TEST>
</TABLE>

[/pre]


You CAN insert extra tags into your output file. You do this by creating a custom TAGSET template to be used with the SAS XML Libname engine. That's the part that you have to figure out by reading the documentation on the XML Libname engine and on tagset template syntax and by closely studying the paper I sent the link for or by contacting SAS Tech Support for help.

To get the top tag <CUST_INFO> into the file, I would probably change the SASRow event -- and as soon as the row identifier (in this case <TEST>) was written out, I'd write out the custom tag. (BTW, you don't show what happens if you have 2 customers, only what the XML for 1 customer would look like.)

Since the tagset template syntax does provide a way to write tags out conditionally, then to get the ending CUST_INFO tag, I'd probably test (in the SASColumn event) for the occurence of the ID variable (by testing the event variable called NAME). Then I would write out the </CUST_INFO> when the NAME event variable was being handled. Probably something like this (warning -- this is untested code):

[pre]
put "</CUST_INFO>" / if cmp(NAME, "ID");
put NL / if cmp(NAME, "ID");

[/pre]

Once you had your custom tagset template, let's say you called it TAGSETS.SPECIAL, you would use it like this:
[pre]
libname xmlout xml "c:\temp\test_custom.xml" tagset=tagsets.special;
data xmlout.test;
set work.test;
run;

libname xmlout clear;

[/pre]

If you need more help with tagset template syntax or the SAS XML Libname engine or designing a changed tagset template to use with the XML Libname engine, then I recommend that you contact SAS Tech Support.

cynthia
New Contributor slb
New Contributor
Posts: 4

Re: XML Layout file structure

I'm probably going to ask for the same information that's posted here again. Please forgive me, this is the first I am trying to import an xml file.

1) I am at step one. Here's what I have:

filename xmlinp 'V:\MICHR\Personal\bruestle\Open Clinica Testing\ExportTest1D20110517142141-0400';

proc cdisc model=odm read=xmlinp;
odm odmversion="1.2";
clinicaldata out=results.ae sasdatasetname="AE";
run;

2) If I run the syntax above I get the following message:

ERROR: Physical file does not exist, V:\MICHR\Personal\bruestle\Open Clinica
Testing\ExportTest1D20110517142141-0400.
encountered during CDISCRDR parsing
occurred at or near line 1, column 1

The file really does exist so I'm thinking the problem is more to do with what's in the file, specifically the very first character.

3) Here's the first line of the file:




Can you help me?
SAS Super FREQ
Posts: 8,740

Re: XML Layout file structure

Hi:
I believe your best resource for this question will be to work with SAS Tech Support. PROC CDISC is designed to work with a particular type of XML (CDISC XML) and the above questions were not, in any way, meant to comment on how you work with PROC CDISC.

In fact, PROC CDISC and the new Clinical Standard Toolkit are probably what you should be using:
http://support.sas.com/rnd/base/cdisc/proccdisc/index.html
http://support.sas.com/rnd/base/cdisc/index.html
http://support.sas.com/rnd/base/cdisc/cst/preproduction.html

If you are having issues using PROC CDISC, your best resource would be to open a track with Tech Support.

cynthia
Ask a Question
Discussion stats
  • 14 replies
  • 571 views
  • 0 likes
  • 4 in conversation