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.
