reading xml file and formatting dates in some of the tables

WendyT — Wed, 09 Feb 2011 20:38:10 GMT

I need some help! I've been going around in the documentation for a couple of days and I'm stumped.

I'm trying to set up a program that will E-mail folks when equipment needs to be maintained, and all of the information I need is contained in an xml file, along with other project information.

I have been able to set up a macro that reads all the individual tables (15) out of the xml file and spits them out to an Excel workbook so I can look at them.

LIBNAME EQUIP XML "\\inputpath\OurEquipmentData.xml" ;

PROC SQL NOPRINT ;
SELECT MEMNAME INTO: TABLENAME SEPARATED BY ' '
FROM SASHELP.VTABLE
WHERE LIBNAME='EQUIP' ;
QUIT;

%MACRO READEQUIP ;

%LET TABLECOUNT=%sysfunc(COUNTW(&TABLENAME)) ;
%DO ZZ=1 %TO &TABLECOUNT ;
%LET DSN=%SCAN(&TABLENAME,&ZZ) ;

DATA &DSN ; SET EQUIP.&DSN ;
RUN ;

PROC EXPORT DATA=WORK.&DSN OUTFILE="\\outputpath\EXCEL_EQUIPMENT_FILE.xlsx" DBMS=EXCEL REPLACE LABEL ; SHEET="&DSN" ;
RUN ;

%END ;
%MEND ;

%READEQUIP ;

However, the datetime format in the xml file is a bit strange, and comes in as text. The informat E8601DT. will do the translation to a SAS datetime.

NEWDATE=INPUT(DATE,E8601DT.);
FORMAT NEWDATE DATETIME23. ;
(or TEMPEXPDATE, as the case may be)

Here's where I'm stuck: 8 of the 15 tables have a datetime variable. In one table, it is named TEMPEXPDATE, and it is DATE in the rest. I can get a listing of the tables that contain "DATE" as part of the variable name from SASHELP.VCOLUMN.

PROC SQL NOPRINT ;
CREATE TABLE DATENAMES AS
SELECT
MEMNAME,
NAME
FROM SASHELP.VCOLUMN
WHERE LIBNAME='EQUIP' AND NAME CONTAINS 'DATE' ;
QUIT;

But I'm not sure how to use that information.

Your help would be very much appreciated!

Wendy T.

Re: reading xml file and formatting dates in some of the tables

Vasile01 — Wed, 09 Feb 2011 20:58:29 GMT

Hi Wendy,

From that last SQL statement, do you think that by transposing the DATENAMES table you'd get a more useful result?

Warm regards,
Vasile

topic Re: reading xml file and formatting dates in some of the tables in SAS Programming

reading xml file and formatting dates in some of the tables

Re: reading xml file and formatting dates in some of the tables