How to convert numeric value (BEST12.) to SAS date (DATETIME16.)

[Sofie3]

Hello,

I imported an excell file with a variable 'time' into SAS.

In excel; the time is written as '2009-03-16 12:42:00,000'.

When I import this in SAS the 'time' is written as '39888.529167'. 

I think this is a 'Julian date' version, when I calculate it, the 'start date 0' = 1900-01-01.

My question is: how do you convert this time variable to DATETIME16. format?

Column attributes: format BEST 12. ; informat 12. ; numeric; length 8

I tried a lot of codes (found on the internet), but nothing worked. I don't know if I filled in the codes correctly. It is sometimes not easy to understand what you have to fill in for some codes...

Here are some used codes:

data ILD1_date; set ILD_1bisx;
input jdt;
dt=jdt/1000+'01JAN1900:0:0'dt;
d=datepart(dt);
t=timepart(dt);
format jdt 12.
       dt  DATETIME16.
       d   mmddyy10.
       t   e8601tm.;
cards;
39888.529167
39888.529861
39888.530556
;

data ILD1_date; set ILD_1bisx;
   orig_var = 'time';
   new_var = input(orig_var,DATETIME16.);
   format new_var best12.;
run;

data ILD1_date; set ILD_1bisx;
   orig_var = '39888.529167';
   new_var = input(orig_var,DATETIME16.);
   format new_var BEST12.;
run;

Here are some time variables:

39888.529167

39888.529861

39888.53125

39888.5375

As you can see, some of the variables are shorter than others.

Thanks in advance.

[Tom]

Looks like your column TIME has DATETIME values (what some systems call TIMESTAMP values), not DATE values.  But it also contains some cells that have non-numeric values.  So SAS created that variable as CHARACTER (it is easy to convert numbers to strings, but not the other way around).  When SAS does this it stores representation of the number in the cell as a string.  Excel stores datetime values as number of days with the time of day as a fraction.  SAS stores datetime values as number of seconds.  To convert your strings you first need to convert them into numbers and then adjust for the difference in base date and way that time of day is coded.  

data test;
  input string $20.;
  number=input(string,32.);
  date=number + '30DEC1899'd;
  datetime=dhms(date,0,0,0);
  format date date9. datetime datetime19.;
cards;
39888.529167
39888.529861
39888.530556
;

proc print;
run;

Results

Obs       string        number          date               datetime

 1     39888.529167    39888.53    16MAR2009     16MAR2009:12:42:00
 2     39888.529861    39888.53    16MAR2009     16MAR2009:12:43:00
 3     39888.530556    39888.53    16MAR2009     16MAR2009:12:44:00

You should watch out for the values in the column that are NOT a datetime value.  Perhaps you have strings that look to humans like datetime values, but that Excel did not consider a datetime value.  Those will probably result in a missing values for the SAS date and datetime values.

Note that the date type formats will ignore the fractional days when displaying the date value in a human readable way.  if you want to keep that date variable you could use INT() function to remove the fractional part of the date (make sure to do that AFTER you have used it to generate a datetime value).

[Sofie3]

Hi,

Thanks for the quick response!

Following your code, I received the correct dates & times (in my results, using the proc print statement).

Is it also possible to get these results in my dataset (with the data; set; procedure)?

I tried this:

data ILD1_date; set ILD_1bisx;
 input string $20.;
  number=input(string,32.);
  date=number + '30DEC1899'd;
  datetime=dhms(date,0,0,0);
  format date date9. datetime datetime19.;

  run;
 

But I do not know where I have to put my variable name (which is 'time').

Thanks a lot!

[ballardw]

---

@Sofie3 wrote:

Hi,

Thanks for the quick response!

Following your code, I received the correct dates & times (in my results, using the proc print statement).

Is it also possible to get these results in my dataset (with the data; set; procedure)?

I tried this:

data ILD1_date; set ILD_1bisx;
 input string $20.;
  number=input(string,32.);
  date=number + '30DEC1899'd;
  datetime=dhms(date,0,0,0);
  format date date9. datetime datetime19.;

  run;
 

But I do not know where I have to put my variable name (which is 'time').

Thanks a lot!

---

If your variable Time is already Character then you cannot place a numeric (SAS datetime value) into it. And you really do not want character values for such anyway. Any manipulation sorting, graphing or using the date or time portions of the values are better off with the datetime value.

You can rename things on the output data set like this:

data ILD1_date (rename=(datetime=time)); 
   set ILD_1bisx;
   input string $20.;
   number=input(string,32.);
   date=number + '30DEC1899'd;
   datetime=dhms(date,0,0,0);
   format date date9. datetime datetime19.;
   drop time;
run;
 

which assumes your had a Time variable in your ILD_1bisx data set. If not then just use

   time=dhms(date,0,0,0);
   format date date9. time datetime19.;

to create time.

[Sofie3]

My time variable in my ILD_1bisx data set is the variable with all these numeric values
39888.529167
39888.529861
39888.530556

When I try this code:
data ILD1_date (rename=(datetime=time));
set ILD_1bisx;
input string $20.;
number=input(string,32.);
date=number + '30DEC1899'd;
datetime=dhms(date,0,0,0);
format date date9. datetime datetime19.;
drop time;
run;

Or this:

data ILD1_date; set ILD_1bisx;
input string $20.;
number=input(string,32.);
date=number + '30DEC1899'd;
datetime=dhms(date,0,0,0);
format date date9. datetime datetime19.;
run;

I always receive this answer in my log:
ERROR: No DATALINES or INFILE statement.

So what I want to become is the conversion of my 'time' variable (with these kind of values 39888.529167) into the datetime variable which was explained by Tom. It worked using his code with the proc print statement, but I want to insert the new values of the datetime variable in my (new) data set ILD1_date. So where do I put the variable 'time' in his code (so SAS reads the value from the data set, not using the 'cards')?

[Tom · Accepted solution]

Do NOT include the INPUT statement when are getting the data via the SET statement.  INPUT is for reading data from lines of text.

Your variable TIME should replace the variable STRING in my example.  I strongly object to calling a variable with DATETIME values in it TIME.  In SAS a TIME value is the number of seconds since midnight.  So a number between '00:00't and '23:59:59.99999't.

data ILD1_date;
  set ILD_1bisx;
  number=input(TIME,32.);
  date=number + '30DEC1899'd;
  datetime=dhms(date,0,0,0);
  format date date9. datetime datetime19.;
  drop time;
run;

[Sofie3]

Thanks! No longer error messages in my log. This was the code I was looking for.

[ballardw]

I am going to guess that you used Proc Import to bring the data into SAS.

Sometimes, not always but sometime, these mixes of character and date values can be resolved by using Excel to save the sheet as CSV and then importing the CSV file. This sometimes works because the text exported for formatted date/time/datetime values will look similar to the character versions and then Proc Import gets a somewhat more consistent look at the values.

Also when importing a CSV file a data step is generated to read the data file and you can copy from the log, paste into the editor and modify to make small changes in the program to read the file.

Excel uses a fraction of a day to indicate "time" values. So 0.5 is half a day or 12 hours. SAS uses days for dates and seconds for time and datetime values. The day 1 for SAS is 1 Jan 1960 and for Excel is 1 Jan 1900, so the portion you thought is Julian date is actually the number of days since 1 Jan 1900 (more or less, Excel versions do flaky things sometimes like some allow 0 Jan 1900 as a valid date). So you can see 100 Excel years (year 2000) is in the 36500 range of values.

If someone forced the Excel file to hold an invalid date such as Feb 29 in a non-leap year or any invalid day for the month that would have had to be a character value. I have seen such in Excel files that are used for manual data entry and that happens because none of the cells in Excel are required to hold any specific type of data.