topic Re: How to: create an xml file in SAS Programming

How to: create an xml file

shlomiohana — Tue, 02 Nov 2021 20:43:41 GMT

Hello,
I want to create an xml file from a SAS table with a hierarchy.
The table has the following 10 fields:
currencyid, value, id, withdrawaldate, withdrawaltime, bankcode, bankbranchcode, monthreport, cuerrentdate, factorcode.

Example table:

bankcode	bankbranchcode	id	withdrawaldate	withdrawaltime	currencyid	monthreport	cuerrentdate	factorcode	value
20	401	12332324	2020-09-12	12:20:24	00	2021-10	2021-11-01	20	400
20	490	12342	2020-09-13	12:08:00	00	2021-10	2021-11-01	20	500

This is the code I wrote:

data _null_;
	set work.report end=eof;
	file "&outfile./Report.xml" noprint encoding="utf-8";
        by id;

	if _n_ =1 then
		do;
			put '<?xml version="1.0" encoding="utf-8"?>';
			put '<ATMstockReport>';
			put '<currentDate>' currentDate '</currentDate>';
			put '<factorCode>' factorCode '</factorCode>';
			put '<monthReport>' monthReport '</monthReport>';
			put '<atmsStock>';
		end;
if first.id then
    do;
	put '<action atmId="'id'">';
	put '<bankCode>' bankCode '</bankCode>';
	put '<bankBranchCode>' bankBranchCode '</bankBranchCode>';
        put '<withdrawaldate>' withdrawaldate '</withdrawaldate>';
        put '<withdrawaltime>' withdrawaltime '</withdrawaltime>';
	put '<currencyvalue>';
end;

	put '<currency dt=" 'currencyid' ">';
	put '<value>' value '</value>';
	put '</currency>';

if last.id then
   do;
	put '</currencyvalue>';
	put '</action>';
end;

	if eofl then
		do;
			put '</atmsStock>';
			put '</ATMstockReport>';
		end;
run;

After running I get only the first record, something with the sorting of the "currencyid" field is incorrect I think.

Do I need to sort the table before in a certain order?

Thanks.

Re: How to: create an xml file

ballardw — Tue, 02 Nov 2021 21:22:36 GMT

You don't really show what you expect the output to look like.

What does your LOG look like for that step? Any interesting notes?

Suggestion: Open the XML in a TEXT file viewer, not something that attempts to render the xml into a pretty output.

See if the lines of text you generated are as you expect.

I doubt that sort order is the issue unless it was enough a problem that the BY would cause an error, such as BY variables not sorted...

Your code uses END=EOF.

You then test for the value of EOF1 so this is one thing to consider. It means that one or more tags such as atmsstock and ATMStockReport do not get the close tags they need.

Re: How to: create an xml file

shlomiohana — Tue, 02 Nov 2021 22:44:29 GMT

In the program, it is written "set work.report end=eof1;" I accidentally wrote eof.

If I delete these 2 lines I get xml but not exactly according to the hierarchy I wanted.
put '<currency dt=" 'currencyid' ">';
put '</currency>';

There are no errors in the Log

Re: How to: create an xml file

ballardw — Wed, 03 Nov 2021 16:38:03 GMT

@shlomiohana wrote:
In the program, it is written "set work.report end=eof1;" I accidentally wrote eof.

If I delete these 2 lines I get xml but not exactly according to the hierarchy I wanted.
put '<currency dt=" 'currencyid' ">';
put '</currency>';

There are no errors in the Log

Not intimately familiar with all the stuff that may be an XML so I don't know what the DT= may mean. If it is related to data type then perhaps you are missing something as the example currencyid values of 00 don't look like a "type" .