BookmarkSubscribeRSS Feed
WendyT
Pyrite | Level 9
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.
1 REPLY 1
Vasile01
Fluorite | Level 6
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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 832 views
  • 0 likes
  • 2 in conversation