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;
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>
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>
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.