How to generate (export) correct XML from SAS

TeryK2 — Sat, 20 Jan 2024 17:18:15 GMT

i need to export data from SAS database to XML file. The XML structure is defined by third parties, so it must be followed.

I created a map file that defines the XML, but I don't know how to merge the data so that some values are not repeated. Basically how to create one table within a table. (I can't replicate the required structure.)

for one company i need to put address and list of all transactions. but now I get 1 record in XML for each row in table.

i need output like below

<CESOP>
    <PaymentDataBody>
        <ReportedPayee>
            <Name>COMPANY1</Name>
            <Address>
                <FreeAddress>Address 1</FreeAddress>
            </Address>
            <ReportedTransaction>
                <trns_ID>TRNS1</trns_ID>
                <trns_CCY>EUR</trns_CCY>
            </ReportedTransaction>
            <ReportedTransaction>
                <trns_ID>TRNS2</trns_ID>
                <trns_CCY>USD</trns_CCY>
            </ReportedTransaction>
        </ReportedPayee>
        <ReportedPayee>
            <Name>COMPANY2</Name>
            <Address>
                <FreeAddress>Address 1</FreeAddress>
            </Address>
            <ReportedTransaction>
                <trns_ID>TRNS1</trns_ID>
                <trns_CCY>EUR</trns_CCY>
            </ReportedTransaction>
        </ReportedPayee>
    </PaymentDataBody>
</CESOP>

but i got this:

<CESOP>
    <PaymentDataBody>
        <ReportedPayee>
            <Name>COMPANY1</Name>
            <Address>
                <FreeAddress>Address 1</FreeAddress>
            </Address>
            <ReportedTransaction>
                <trns_ID>TRNS1</trns_ID>
                <trns_CCY>EUR</trns_CCY>
            </ReportedTransaction>
        </ReportedPayee>
        <ReportedPayee>
            <Name>COMPANY1</Name>
            <Address>
                <FreeAddress>Address 1</FreeAddress>
            </Address>
            <ReportedTransaction>
                <trns_ID>TRNS2</trns_ID>
                <trns_CCY>USD</trns_CCY>
            </ReportedTransaction>
        </ReportedPayee>
        <ReportedPayee>
            <Name>COMPANY2</Name>
            <Address>
                <FreeAddress>Address 1</FreeAddress>
            </Address>
            <ReportedTransaction>
                <trns_ID>TRNS1</trns_ID>
                <trns_CCY>EUR</trns_CCY>
            </ReportedTransaction>
        </ReportedPayee>
    </PaymentDataBody>
</CESOP>

SAS code:

data COMP;
    length COMPANY $200. ADDRESS $200.  COUNTRY $200. TRNS_ID $200. TRNS_CCY $3;
    infile datalines dlm = '|';
    input COMPANY ADDRESS COUNTRY TRNS_ID TRNS_CCY;
    datalines;
COMPANY1|Address 1|CZ|TRNS1|EUR
COMPANY1|Address 1|CZ|TRNS2|USD
COMPANY2|Address 1|DE|TRNS1|EUR
run;

filename out 'path\compout.xml';
libname out xmlv2 xmltype=xmlmap xmlmap='path\test.map';

data out.COMP;
   set COMP;
run;

Re: How to generate (export) correct XML from SAS

Tom — Sat, 20 Jan 2024 21:44:29 GMT

An XML file is just a text file. So just use normal SAS methods for writing text files.

data _null_;
  file OUT ;
  if _n_=1 then put '<CESOP>' / @3 '<PaymentDataBody>';
  if eof then put @3 '</PaymentDataBody>' / '</CESOP>';

  set comp end=eof;
  by company ;
  if first.company then put 
   @5 '<ReportedPayee>'
 / @7 '<Name>' COMPANY +(-1) '</Name>'
 / @7 '<Address>'
 / @9 '<FreeAddress>' ADDRESS +(-1) '</FreeAddress>'
 / @7 '</Address>'
  ;
  put 
   @7 '<ReportedTransaction>'
 / @9 '<trns_ID>' TRNS_ID +(-1) '</trns_ID>'
 / @9 '<trns_CCY>' TRNS_CCY +(-1) '</trns_CCY>'
 / @7 '</ReportedTransaction>'
  ;
  if last.company then put 
   @5 '</ReportedPayee>'
  ;
run;

Result

<CESOP>
  <PaymentDataBody>
    <ReportedPayee>
      <Name>COMPANY1</Name>
      <Address>
        <FreeAddress>Address 1</FreeAddress>
      </Address>
      <ReportedTransaction>
        <trns_ID>TRNS1</trns_ID>
        <trns_CCY>EUR</trns_CCY>
      </ReportedTransaction>
      <ReportedTransaction>
        <trns_ID>TRNS2</trns_ID>
        <trns_CCY>USD</trns_CCY>
      </ReportedTransaction>
    </ReportedPayee>
    <ReportedPayee>
      <Name>COMPANY2</Name>
      <Address>
        <FreeAddress>Address 1</FreeAddress>
      </Address>
      <ReportedTransaction>
        <trns_ID>TRNS1</trns_ID>
        <trns_CCY>EUR</trns_CCY>
      </ReportedTransaction>
    </ReportedPayee>
  </PaymentDataBody>
</CESOP>

topic How to generate (export) correct XML from SAS in SAS Programming

How to generate (export) correct XML from SAS

Re: How to generate (export) correct XML from SAS