BookmarkSubscribeRSS Feed
jotabe
Calcite | Level 5

Hi all

 

I have a XML file as follow:

 

<?xml version="1.0" encoding="Windows-1252"?>
<new_examples>
    <element_first>

        <type>123</type>

        <state>1</state>

    </element_first>

    <examples_list>

        <example_element>

            <name>a</name>

            <status>1</status>

        </example_element>

        <example_element>

            <name>b</name>

            <status>3</status>

        </example_element>

    </examples_list>

</new_examples>

 

Then I have a table Table1 like:

Name Amount

a         3

b         12

 

And I want to add the amount to the XML so I end up with:

<?xml version="1.0" encoding="Windows-1252"?>
<new_examples>
    <element_first>

        <type>123</type>

        <state>1</state>

    </element_first>

    <examples_list>

        <example_element>

            <name>a</name>

            <status>1</status>

            <amount>3</amount>

        </example_element>

        <example_element>

            <name>b</name>

            <status>3</status>

            <amount>12</amount>

        </example_element>

    </examples_list>

</new_examples>

 

I read the XML as:

 

filename xx temp;
libname xx xmlv2 '\\path\test_xml' automap=replace xmlmap=xx;

 

 

It lets me access the information of the XML in SAS, having the table example_element with the two example_element in the list.

 

But then I don't know how I can modify the XML as if I try to overwrite the table xx.example_element but it does not work.

 

I am trying first to do a dummy example adding a fixed amount = 10 in all the example_element like:

 

data xx.example_element;
set xx.example_element; amount = 10; run;

This is the log:

 

WARNING: Output column amount does not occur in XMLMap definition of table example_element.
ERROR: XMLMap for output table example_element requested but that table did not exist in the XMLMap output section.
ERROR: Invalid sequence of commands for file XX.example_element.DATA.

 

 

 

I am using SAS Enterprise Guide 7.1

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

In your example, you let SAS define the XML map from the existing file.

Since the existing file does not have an AMOUNT value, the map doesn't either, and therefore you can't insert values into a column that doesn't exist.

 

The best option is to modify the map. If you run

 data tt; infile XX; input; put _infile_; run;

you see that SAS created this map:

 

<?xml version="1.0" encoding="UTF-8"?>
<!-- ############################################################ -->
<!-- 2018-06-07T15:13:13 -->
<!-- SAS XML Libname Engine Map -->
<!-- Generated by XML Mapper, 904000.0.0.20130522190000_v940 -->
<!-- ############################################################ -->
<!-- ###  Validation report                                   ### -->
<!-- ############################################################ -->

<!-- XMLMap validation completed successfully. -->
<!-- ############################################################ -->
<SXLEMAP name="AUTO_GEN" version="2.1">
    <NAMESPACES count="0"/>
    <!-- ############################################################ -->
    <TABLE description="new_examples" name="new_examples">
        <TABLE-PATH syntax="XPath">/new_examples</TABLE-PATH>
        <COLUMN class="ORDINAL" name="new_examples_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
    <!-- ############################################################ -->
    <TABLE description="element_first" name="element_first">
        <TABLE-PATH syntax="XPath">/new_examples/element_first</TABLE-PATH>
        <COLUMN class="ORDINAL" name="new_examples_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="element_first_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/element_first</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="type">
            <PATH syntax="XPath">/new_examples/element_first/type</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="state">
            <PATH syntax="XPath">/new_examples/element_first/state</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
    <!-- ############################################################ -->
    <TABLE description="examples_list" name="examples_list">
        <TABLE-PATH syntax="XPath">/new_examples/examples_list</TABLE-PATH>
        <COLUMN class="ORDINAL" name="new_examples_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="examples_list_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/examples_list</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
    <!-- ############################################################ -->
    <TABLE description="example_element" name="example_element">
        <TABLE-PATH syntax="XPath">/new_examples/examples_list/example_element</TABLE-PATH>
        <COLUMN class="ORDINAL" name="examples_list_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/examples_list</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="example_element_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/new_examples/examples_list/example_element</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="name">
            <PATH syntax="XPath">/new_examples/examples_list/example_element/name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>1</LENGTH>
        </COLUMN>
        <COLUMN name="status">
            <PATH syntax="XPath">/new_examples/examples_list/example_element/status</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
    </TABLE>
</SXLEMAP>

 

Create a new map and add a new section for the new column

 

        <COLUMN name="amount">
            <PATH syntax="XPath">/new_examples/examples_list/example_element/amount</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

and use that map to write out the new table.

 

 

 

 

jotabe
Calcite | Level 5

Thanks for your reply ChrisNZ.

 

However, this is the first time I work with XML in SAS and I am quite lost.

 

I do not know how to "Create a new map and add a new section for the new column" neither "use that map to write out the new table", Also not sure how I can once I have that new table, how I end up with a XML as the one in my example?

 

I would appreciate a code step by step if that's not too much on how to add the amount = 10 for both, it looks like it must be a couple of easy data steps but I don't know what I should do.

 

I am trying to see how to do this in the documentation but I cannot see anything similar to what I need, just how to read in XMLs and how to export easy XMLs (with less complexity than the one I have). So if someone could point me out what I should look for in the documentation. This is the first time I struggle with SAS documentation until the point of opening a question in this forum and netiher I find a similar question in the forum...

ChrisNZ
Tourmaline | Level 20

Here is a working example. The map of your output "table", i.e. XML file, must be similar to the one below.

 


data _null_;
  file "%sysfunc(pathname(WORK))\output.map" lrecl=32000;
  put'
<?xml version="1.0" encoding="UTF-8"?>

<SXLEMAP version="1.9">   

  <OUTPUT>     
    <HEADING>       
      <ATTRIBUTE name="description" value="xml output"/>     
    </HEADING>     
    <TABLEREF name="OUTTABLE"/>   
  </OUTPUT> 
 
  <TABLE name="OUTTABLE">     
    <TABLE-PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE</TABLE-PATH>     
    <COLUMN name="DESCRIPTION">       
      <PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE/DESCRIPTION</PATH>       
      <TYPE>character</TYPE>       
      <DATATYPE>string</DATATYPE>       
      <LENGTH>50</LENGTH>     
    </COLUMN>     
    <COLUMN name="COUNT">       
      <PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE/COUNT</PATH>       
      <TYPE>numeric</TYPE>            
      <DATATYPE>integer</DATATYPE>       
    </COLUMN>     
    <COLUMN name="PERCENT">       
      <PATH syntax="XPath">/xml/lev1/lev2/OUTTABLE/PERCENT</PATH>       
      <TYPE>numeric</TYPE>       
      <DATATYPE>double</DATATYPE>       
    </COLUMN>   
  </TABLE>

</SXLEMAP> ';
;run ;

    
filename OUT    "%sysfunc(pathname(WORK))\output.xml";
filename OUTMAP "%sysfunc(pathname(WORK))\output.map";
libname  OUT xml92 xmlmap=OUTMAP;

data OUT.OUTTABLE;              
  length DESCRIPTION $100;
  retain  DESCRIPTION 'abc' COUNT 22 PERCENT 2.2;
run;


%* Dump contents of output file;
data _null_; infile OUT; input; put _infile_ ; run;

<?xml version="1.0" encoding="windows-1252" ?>
<!--
      SAS XML Libname Engine (SAS92XML)
      SAS XMLMap Generated Output
      Version 9.04.01M2P07232014
      Created 2018-06-08T12:19:09
  -->

<xml description="xml output">
<lev1>
<lev2>
<OUTTABLE>
<DESCRIPTION>abc</DESCRIPTION>
<COUNT>22</COUNT>
<PERCENT>2.2</PERCENT>
</OUTTABLE>
</lev2>
</lev1>
</xml>

 

 

 

ChrisNZ
Tourmaline | Level 20

@maggiem_sas 

 

Sadly most SAS developers these days have to endure SAS EG with nothing else available, because of SAS Institute licencing choices.

 

I wish SAS employees ate the same dog food and only used EG as well. This would without a doubt ensure swift changes.

 

maggiem_sas
SAS Employee

The SAS XML Mapper does not require a license.

ChrisNZ
Tourmaline | Level 20

Regardless, XML mapper never gets deployed with EG. It is probably not even supplied, and can certainly not be installed by downloading it in most corporations. I recall XML mapper used to get deployed with desktop SAS, together with graph'n'go, licence updater, etc.

I am only mentioning the licence because that's how SAS Institute killed SAS desktop.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2506 views
  • 0 likes
  • 3 in conversation