0

I have a table that has some basic info about patients named outpatient:

ID   Date
A    10/04/11
B    10/18/11
C    11/30/11

In SAS when I check the Date column, I see that it has format MMDDYY8. and informat DATETIME20. For another step where I need to find the min Date:

proc sql;
create table outpatient2 as
select distinct ID, min(Date) as first_date datetime.
from outpatient
group by ID;
quit;

When I check the output it looks like this:

ID  first_date
A   01JAN60:05:15:05
B   01JAN60:05:15:03
C   01JAN60:05:15:52

Because I had no time, my bad fix around was to send the outpatient table to oracle and call to it using this:

from userid.outpatient

I did check the oracle version in sas by setting it in a data step and it seems it is format and both informat DATETIME20. But using the bad fix around I get the correct output I was looking for:

ID  first_date
A   05OCT11:00:00:00
B   25MAY11:00:00:00
C   09AUG11:00:00:00

Please disregard the dates exactly, they're sort of a random example to show that when Date is formatted MMDDYY8. and min(Date) is used, it returns an odd date but if Date is formatted DATETIME20. it returns the correct date.

Edit: There are no blank DATEs and although the outpatient2 step completes, I get an ERROR: Date value out of range.

PinkyL
  • 341
  • 1
  • 8
  • 19
  • If you have an informat of datetime and format of mmddyy8 your source data is represented incorrectly. You say you fixed it but not how, so hard to say what is wrong. Your informat/format need to match regarding datetime/date ie. an informal datetime needs a datetime format and a date informat needs a date format. – Reeza Mar 20 '15 at 17:36
  • Sorry, but when I mentioned the bad fix around it literally was sending it to oracle and calling the table that went to oracle. I'm guessing I need to format it to DATETIME20. as well? – PinkyL Mar 20 '15 at 18:16

2 Answers2

1

See if you can get the date from the variable using the DATEPART function. There is also a TIMEPART as well.

ActualDate=datepart(date);
ActualTime=timepart(date);
format actualdate mmddyy8. actualtime time.;
Jay Corbett
  • 28,091
  • 21
  • 57
  • 74
1

You cannot convert data from date to datetime using formats alone, you need to use functions. Additionally, you have an incorrect informal set in your original dataset, datetime formats shouldn't apply to date variables.

Here's an example of replicating your problem and the correct way to get the result based on your data. 

*Generate sample data with properties described in question;
data have;
informat dt_bad datetime20.;
format dt_bad mmddyy8.;
dt_bad=mdy(2, 12, 2014); output;
dt_bad=mdy(3, 13, 2014); output;
dt_bad=mdy(4, 20, 2013); output;
run;

proc print data=have;
run;

*Your code which generates incorrect output;
proc sql;
create table wrong_output as
select min(dt_bad) as bad_date format=datetime20.
from have;
quit;

proc print data=wrong_output;
run;

*one correct way to convert a date variable to datetime variable;
proc sql;
create table correct_output as
select dhms(min(dt_bad), 0, 0, 0) as good_date format=datetime20.
from have;
quit;

proc print data=correct_output;
run;
Reeza
  • 20,510
  • 4
  • 21
  • 38