One process for both DATE and DATETIME

[deleted_user]

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?

[Olivier]

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 ;

[SPR]

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.

[Olivier]

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