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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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