topic Re: How do I Export to XML in SAS Enterprise Guide

How do I Export to XML

Kran — Tue, 31 Oct 2017 10:06:39 GMT

Hello dear experts,

I need to export datasets into xml files and need some help. I am on SAS 9.4.

Example DATA from my datasets in tabular form structure:

ServiceNumber CustomerDetails NumOfServices ServiceDetails1 ServiceDetails2 AdditionalDetails

123 aaa 1 x y z

124 bbb 2 x y z

125 ddd 1 x y z

Example DATA format expected in XML:

<?xml version="1.0" encoding="xxxxxxxx" ?>
- <DataofServices>
      - <Services>
             <ServiceNumber>123</ServiceNumber>
              <CustomerDetails>aaa</CustomerDetails>
              <NumOfServices>1</NumOfServices>
             - <ServiceDetails>

        </Services>
       - <Services>
             <ServiceNumber>124</ServiceNumber>
              <CustomerDetails>bbb</CustomerDetails>
              <NumOfServices>2</NumOfServices>
             - <ServiceDetails>

- <ServiceDetails>

</Services>

</DataofServices>

From what I have tried I am able to output the xml with a simple hierarchy, but not the nested type xml where multiple entries can exist based on data count in the third column as shown in the sample.

As you can see, the ServiceDetails1&2 get nested under ServideDetails based on the count value of NumOfServices.

XML generation is new to me so I searched online, but it seems that most information is for importing XML and most information on exporting is just to export the table line-by-line in XML, not the nested way with differing number of nested values as above.

Pls help and Thanks in advance!

Re: How do I Export to XML

RW9 — Tue, 31 Oct 2017 10:29:58 GMT

If you want a more complicated XML output, then you will likely need to write this yourself using datastep and put statements. At the end of the day, XML is just plain text with tags, so you can create it any way you like. Some pseudocode to get you going:

data _null_;
  set have end=last;
  file "c:\want.xml";
  array servicedetails{*};
  if _n_=1 then do;
    put '<?xml version="1.0" enconding="xxxxx" ?>';
    put '<DataofServices>';
  end;
  put '<ServiceNumber>' put(servicenumber,z3.) '</ServiceNumber>';
  ...
  do i=1 to dim(servicedetails);
    put '<ServiceDetails>' sericedetails{i} '</ServiceDetails>';
  end;
  ...
  if last then put '</DataofServices>';
run;

Re: How do I Export to XML

Kran — Wed, 01 Nov 2017 17:18:42 GMT

Hey thanks for your reply CW9..

so I tried it this below way..

PROC TEMPLATE;
DEFINE TAGSET Tagsets.xmlexport /STORE=work.templat; INDENT=5;
DEFINE EVENT doc;
    START:
    /*put required declarations in XML*/
       PUT "<?xml version=""1.0""";
       PUTQ " encoding=" ENCODING;
       PUT " ?>";
       PUT "";
       PUT "<Servs>";
       EVAL $counter 0;
       EVAL $curr_claim "-";
  FINISH:
  PUT "</Serv>";
  PUT "</Servs>";
END;

DEFINE EVENT row;
START:
TRIGGER data /IF CMP(section,"body");
END;

DEFINE EVENT data;
START:
       DO /IF CMP(name,"ServiceNumber");
           DO /IF CMP($curr_claim,"-") or (^CMP($curr_claim,"-") and ^CMP($curr_claim,value));
               EVAL $line_num 1;
               PUT "</Serv>" /IF ^CMP($curr_claim,"-");
               SET $curr_claim value;
               PUT "<Serv>";
               PUT "<ServiceNumber>" VALUE "</ServiceNumber>";
               PUT "<CustomerDetails>" VALUE "</CustomerDetails>";
               PUT "<NumOfServices>" VALUE "</NumOfServices>";
               PUT "<ServiceDetails>";
            ELSE;
            DO /IF CMP($curr_claim,value);
               EVAL $line_num $line_num + 1;
               PUT "<Details>";
           DONE;
        DONE;
    DONE;
     PUT "<ServiceDetails1>" VALUE /if cmp(name,"ServiceDetails1");
     PUT "</ServiceDetails1>" /if cmp(name,"ServiceDetails1");
     PUT "<ServiceDetails2>" VALUE /if cmp(name,"ServiceDetails2");
     PUT "</ServiceDetails2>" /if cmp(name,"ServiceDetails2");
     PUT "</Details>" /IF CMP(name,"ServiceDetails2");
     PUT "<AdditionalDetails>" VALUE /if cmp(name,"AdditionalDetails");
     PUT "</AdditionalDetails>" /if cmp(name,"AdditionalDetails");

END;
END;
RUN;

This above code seems to work for multiple service details nested in ...!, but I keep getting same value as ServiceNumber for CustomerDetails and NumOfServices (and yes I know why.. 😄 )

but I need help with how to get them out of the do/if condition for the service details part ..but yet, come in the order as required xml file..

</Serv>

Thanks in advance! 🙂

Re: How do I Export to XML

RW9 — Thu, 02 Nov 2017 09:07:04 GMT

Please avoid coding all in upcase, it really makes code hard to read and via the web just looks like shouting.

I had not realised you wanted to do this via template code, I am afraid I have not done that so can't help there, the code I provided was a simple datastep write, then you can use looping and such like. I know looping effect is possible in template, but as I haven't done any in ages it would mean reading up on the subject again.