topic Re: need help in generating xml file using sas in z/os in SAS Programming

need help in generating xml file using sas in z/os

kyesrao — Tue, 13 Mar 2018 05:12:47 GMT

Hi,

On Z/OS Mainframe SAS, I was trying to find a way to create an xml file from the SAS dataset created by pulling DB2 data.

However, I was not successful. Please help.

Thanks,

kyesrao.

Re: need help in generating xml file using sas in z/os

andreas_lds — Tue, 13 Mar 2018 08:16:50 GMT

What have you tried so far? Which SAS version is available? Check out libname xml.

Re: need help in generating xml file using sas in z/os

kyesrao — Tue, 13 Mar 2018 13:55:34 GMT

Version9.4.
I did not where to start. Your email helped me where to start.
I will start researching further with your inputs libname xml.

Thank you.

Re: need help in generating xml file using sas in z/os

Cynthia_sas — Tue, 13 Mar 2018 14:20:40 GMT

Hi:

Here's a simple example using just 3 obs from SASHELP.CLASS:

Here's the tip sheet on using the LIBNAME XML engine: https://support.sas.com/rnd/base/xmlengine/XML94tipsheet.pdf

cynthia

Re: need help in generating xml file using sas in z/os

kyesrao — Tue, 13 Mar 2018 20:27:34 GMT

Hi Cynthia,
I think I am doing something wrong that I am not able to find out.
Here is what I have in my job.

PROC SQL;
CONNECT TO DB2 (SSID=SYDP);
%PUT &SQLXMSG;

CREATE TABLE CVDDATA AS
SELECT * FROM CONNECTION TO DB2
(SELECT CVD_EMPLOYEE_ID AS EMEID
,CVD_MEMBER_ID AS MBRID
,CVD_EDATE AS CVDEDT
,CVD_TDATE AS CVDTDT
FROM EIPRDADM.EITBAS_COVDETAIL
WHERE CURRENT_DATE BETWEEN CVD_EDATE AND CVD_TDATE
AND CVD_COVERAGE_CD LIKE 'H%'
AND CVD_EMPLOYEE_ID ='0000000192' );

%PUT &SQLXMSG;
DISCONNECT FROM DB2;
%PUT &SQLXMSG;

PROC PRINT DATA=CVDDATA;
TITLE '<=====CVDDATA=====>';
FORMAT CVDEDT YYMMDD10.;
FORMAT CVDTDT YYMMDD10.;

FILENAME OUT 'C:\Users\b869363\Desktop\CVDOUT.xml';
LIBNAME OUT XML92 XMLTYPE=XMLMAP
XMLMAP='C:\Users\b869363\Desktop\CVDexport.map';

DATA OUT.CVDOUT;
SET CVDDATA;

The PROC PRINT shows like this.
<=====CVDDATA=====>

Obs EMEID MBRID CVDEDT CVDTDT

1 0000000192 0000000412 2016-09-01 9999-12-31
2 0000000192 0000000192 2016-09-01 9999-12-31
3 0000000192 0000000758 2016-07-01 9999-12-31
******************************** BOTTOM OF DATA ***********

However, the next statement which is FILENAME is reported as error as shown
below. Please help.

27 FILENAME OUT 'C:\Users\b869363\Desktop\CVDOUT.xml';
ERROR: Invalid file, C:\USERS\B869363\DESKTOP\CVDOUT.XML.
ERROR: Error in the FILENAME statement.
27 !
28 LIBNAME OUT XML92 XMLTYPE=XMLMAP
29 XMLMAP='C:\Users\b869363\Desktop\CVDexport.map';

ERROR: Error in the LIBNAME statement.

Re: need help in generating xml file using sas in z/os

Patrick — Wed, 14 Mar 2018 09:48:47 GMT

@kyesrao

Your code runs on the server side which happens to be a Mainframe. A path like C:\Users\b869363\Desktop\CVDOUT.xml is certainly not valid on a Mainframe.

Re: need help in generating xml file using sas in z/os

Cynthia_sas — Wed, 14 Mar 2018 13:34:02 GMT

If this job is going to run on the mainframe, you'll probably have to move your XML Map file up there, too. My recommendation, if this is not working for you, and you know that you are running SAS 9.2 or higher on your mainframe, then you might want to open a track with Technical Support for quicker help.

cynthia

Re: need help in generating xml file using sas in z/os

kyesrao — Mon, 19 Mar 2018 03:38:14 GMT

As Cynthia suggested, I did open a track and it helped me generate the XML document even without using an XML schema.

Instead of these 3 statements,

FILENAME OUT 'C:\Users\b869363\Desktop\CVDOUT.xml';
LIBNAME OUT XML92 XMLTYPE=XMLMAP
XMLMAP='C:\Users\b869363\Desktop\CVDexport.map';

I had to code this one line.

LIBNAME OUT XMLV2

where LIBNAME is the reserved word, XMLV2 is the engine name and OUT is the DDNAME assigned to the following dataset.

DD DSN=EI.BAS.TESTXML.D180310,DISP=(,CATLG),
UNIT=SMSDA,SPACE=(CYL,(100,2),RLSE),
DCB=(RECFM=VB,LRECL=80,BLKSIZE=0),RETPD=180

Re: need help in generating xml file using sas in z/os

kyesrao — Mon, 19 Mar 2018 03:42:05 GMT

Here is the final output.

<?xml version="1.0" ?>
<TABLE>
   <CVDOUT>
      <EMEID>0000854192</EMEID>
      <MBRID>0000319496</MBRID>
      <CVDEDT>2016-09-01</CVDEDT>
      <CVDTDT>9999-12-31</CVDTDT>
   </CVDOUT>
   <CVDOUT>
      <EMEID>0000854192</EMEID>
      <MBRID>0000854192</MBRID>
      <CVDEDT>2016-09-01</CVDEDT>
      <CVDTDT>9999-12-31</CVDTDT>
   </CVDOUT>
   <CVDOUT>
      <EMEID>0000854192</EMEID>
      <MBRID>0000867758</MBRID>
      <CVDEDT>2016-07-01</CVDEDT>
      <CVDTDT>9999-12-31</CVDTDT>
   </CVDOUT>

</TABLE>