Desktop productivity for business analysts and programmers

One process for both DATE and DATETIME

Reply
N/A
Posts: 0

One process for both DATE and DATETIME

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?
Super Contributor
Posts: 260

Re: One process for both DATE and DATETIME

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 ;
Super Contributor
Super Contributor
Posts: 365

Re: One process for both DATE and DATETIME

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.
Super Contributor
Posts: 260

Re: One process for both DATE and DATETIME

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
Ask a Question
Discussion stats
  • 3 replies
  • 91 views
  • 0 likes
  • 3 in conversation