topic Re: Converting datetime in SAS in SAS Programming

Converting datetime in SAS

jscer — Thu, 30 Jan 2025 21:35:08 GMT

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?

Re: Converting datetime in SAS

ballardw — Thu, 30 Jan 2025 22:14:32 GMT

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.

Re: Converting datetime in SAS

quickbluefish — Thu, 30 Jan 2025 22:18:22 GMT

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;

Re: Converting datetime in SAS

ChrisNZ — Thu, 30 Jan 2025 22:23:47 GMT

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

Re: Converting datetime in SAS

Ksharp — Fri, 31 Jan 2025 02:03:33 GMT

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

Re: Converting datetime in SAS

jscer — Fri, 31 Jan 2025 06:23:31 GMT

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…))?

Re: Converting datetime in SAS

Kurt_Bremser — Fri, 31 Jan 2025 07:56:19 GMT

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.