Converting datetime in SAS

[jscer]

I want to convert a string „31.12.1970 00:00:00“ to a SAS Date (seconds since 1960) in order to insert it into a sql server table. Is there any easy way to do so?

[ballardw]

Do your actual values have a time component? I ask because sometimes we have uses of "datetime" values that are all 00:00:00 for time and the problem is a tad bit easier if we don't have to actually process a time component.

DT below has the desired datetime value.

data example;
  string='31.12.1970 00:00:00';
  date = input(scan(string,1,' '),ddmmyy10.);
  dt = dhms(date,0,0,0);
  format date date9.;
run;

If there is actual time value

data example2;
  string='31.12.1970 01:12:45';
  date = input(scan(string,1,' '),ddmmyy10.);
  time = input(scan(string,2,' '),time.);
  dt = dhms(date,0,0,time);
  format date date9. time time8.;
run;

The DHMS function takes a date, hour, minute and second value to create a datetime. So if the "time" is actually 0 hours, 0 minutes and 0 seconds we can use those and not bother to actually read the time component.

If a value is already a time value placing it in the seconds position suffices to provide the hours and minutes but you do need the 0 for the hour and minute.

[jscer]

I used this without the final formatting and got my desired number of seconds since 1960. Somehow the proc sql insert won‘t work, because the number, that should be put in my SQLServer Tabel column of type „datetime“, due to a type mismatch.

Is there a difference between the output of datetime() and a number that i get from dhms(mdy(blabla…))?

[Kurt_Bremser]

If your SAS variable is not formatted as a datetime, the ACCESS module you use to connect to the database does not know that the target should be of type datetime. Apply an appropriate format in SAS.

[quickbluefish]

Someone on here will have a slick way to do this, but crudely, you could do this:

data test;
dtstring="31.12.1970 00:00:00";
dtpart=scan(dtstring,1,' ');
timepart=scan(dtstring,2,' ');
dt_time=dhms(
    mdy(scan(dtpart,2,'.')*1, scan(dtpart,1,'.')*1, scan(dtpart,3,'.')*1),  /* this is the date*/
    scan(timepart,1,':')*1,  /* hour */
    scan(timepart,2,':')*1,  /* minute */
    scan(timepart,3,':')*1  /* second */
    );
format dt_time datetime.;
drop dtpart timepart;
run;

[ChrisNZ]

The input function only reads what it needs.

data EXAMPLE;
  STR  = '31.12.1970 00:00:00';
  DATE = input(STRING, ddmmyy10.);
  putlog DATE= date9.;
run;  

DATE=31DEC1970

[Ksharp]

options datestyle=dmy;
data EXAMPLE;
  STRING  = '31.12.1970 00:00:00';
  DATE = input(STRING, anydtdtm32.);
  format date e8601dt.;
run; 
proc print;run;