|
Thanks dmbuffum,
I'll check out your solution...
While you wrote me your reply, I made my own workaround, and though I'd
share it here for future reference. You can import this particular
problem column by converting the column data to characters using the
dataset option 'DBSASTYPE' to import the column PER_KEY (which, in my
case was a Timestamp(6) format) as follows:
PROC SQL;
CREATE TABLE TEMP AS
SELECT DISTINCT PER_KEY
FROM WORK(DBSASTYPE=(PER_KEY='CHAR(28)'));
; QUIT;
This gets you a character string of Oracle's timestamp format. You
will probably need to convert this to a workable date, time and/or
datetime stamp. I am including a short macro that does this for you.
You should modify to your needs, as it creates (and drops) new
variables named DATE, TIME, and STIME. If you think you have these
variables on your dataset, you should modify this code.
The reason I create and drop these vars is that, in addition to
readability of code, Oracle Timestamp(6) format has a quirk (IMHO) in
that it denotes midnight as '12.00.00.000000 AM' rather than an
expected '00.00.00.000000 AM'. This macro fixes that and then converts
the original timestamp into a SAS datetime stamp. Feel free to use and
recommend any improvements to this code.
Thanks,
Matt
%MACRO FIX_TIMESTAMP(DATA=,DATETIMEVAR=PER_KEY);
DATA &DATA;
SET &DATA(RENAME=(&DATETIMEVAR=DT));
DATE = INPUT(SCAN(DT,1,' '),DATE9.);
STIME = SUBSTR(DT,INDEX(DT,' ')+1);
IF STIME=:'12.' AND INDEX(STIME,'AM')>0 THEN SUBSTR(STIME,1,3) =
'00.';
TIME = INPUT(STIME,TIME.);
&DATETIMEVAR = DHMS(DATE,HOUR(TIME),MINUTE(TIME),SECOND(TIME));
FORMAT &DATETIMEVAR DATETIME.;
DROP DATE TIME STIME;
RUN;
%MEND FIX_TIMESTAMP;
|
