topic Re: One process for both DATE and DATETIME in SAS Enterprise Guide

One process for both DATE and DATETIME

deleted_user — Fri, 07 Jul 2006 18:39:52 GMT

I've got an ETL job that I want to be able to attach to multiple databases. One database has dates stored as DATETIME's and the other only as DATE's.

Is it possible to use one set of steps to convert these all to consistant SAS dates?

Re: One process for both DATE and DATETIME

Olivier — Tue, 11 Jul 2006 13:12:28 GMT

You should consider using this little program... (In a Code window.)

%LET dsIn = ... ; /* your existing DATETIME dataset*/
%LET dsOut = ... ; /* your converted dataset ; you can use the same name twice, and you will have your existing dataset replaced */
/*************************************************/
/* DO NOT EDIT ANYTHING AFTER THIS LINE */
/*************************************************/
PROC CONTENTS DATA = &dsIn (KEEP = _numeric_)
OUT = work.dictionary
NOPRINT ;
RUN ;
PROC SQL NOPRINT ;
SELECT name INTO : varToConvert SEPARATED BY " "
FROM work.dictionary
;
QUIT ;
DATA &dsOut ;
SET &dsIn ;
ARRAY datetimes &varToConvert ;
DO OVER dateTimes ;
dateTimes = DATEPART(dateTimes) ;
END ;
FORMAT &varToConvert DDMMYY10. ;
RUN ;

Re: One process for both DATE and DATETIME

SPR — Thu, 28 Sep 2006 19:12:36 GMT

I noticed that the DATEPART function applied to a variable having date format (without time part) produces completely wrong results. So it is necessary to be sure that DATEPART is always applied to date-time variables.

Re: One process for both DATE and DATETIME

Olivier — Fri, 29 Sep 2006 13:05:11 GMT

The last remark is true. I should have added a WHERE statement in the SQL procedure. Something like :

WHERE UPCASE(format) = "DATETIME"

should focus conversion on DateTime variables only.
Message was edited by: Olivier at Sep 29, 2006 9:08 AM