topic Re: date time variable not properly formatting in SAS Programming

date time variable not properly formatting

SASforHealth — Tue, 10 Mar 2020 17:44:15 GMT

Hi all,

I am hoping to get some help with a date time variable I have imported in an xlsx file. The initial variable in excel was Incident_DateTime with values like 11/10/2018 22:48 . All values are Jan 2017-Feb 29 2020. Upon importing the value, Incident_DateTime became a character value $32000.

Prior to importing Incident_Date = 11/10/2018 22:48

Upon importing the value into SAS incident_datetime= 43414.950532407405

I am hoping to extract from this value, day of the week, month, year, time, hour of the day.

any help?!

I have tried and been unsuccessful with the below codes:

Date =DATEPART(Incident Date/Time);

Time= TIMEPART (Incident Date/Time);

format date datetime18. time time10.;
day = weekday(datepart(date));

format date mmddyy10.;
yrincident=year(date);
monthincident=month(date);

Re: date time variable not properly formatting

jimbarbour — Tue, 10 Mar 2020 17:53:33 GMT

Well, first, remember that there is a conversion that is necessary between Excel datetime values and SAS datetime values. Have you applied this conversion?

See: Excel to SAS date, time, and date time conversion

Jim

Re: date time variable not properly formatting

Kurt_Bremser — Tue, 10 Mar 2020 18:03:27 GMT

Add

'30dec1899'd

to this number, then multiply by 86400, and assign a datetime format to the variable.

Re: date time variable not properly formatting

jimbarbour — Tue, 10 Mar 2020 20:34:55 GMT

@SASforHealth ,

My original response had the right idea but incorrect implementation. See subsequent response.

~~The value you gave looks like a valid Excel datetime in the range you described.~~

~~If I run the following code:~~

DATA	_NULL_;
	Incident_Datetime	=	43414.950532407405;
	Incident_Datetime	=	(Incident_Datetime	+	(21916	*	86400));

	PUTLOG	"NOTE:  "	Incident_Datetime=	DateTime21.;
RUN;

~~I get the following in my log:~~

NOTE:  Incident_Datetime=02JAN2020:12:03:35

~~Which appears to be a date in the range you mentioned. You should be able to then apply all the standard SAS date functions to get month, day of week, etc.~~

~~The key thing I think is to add the conversion factor to the Excel datetime value (21916 * 86400).~~

Good luck,

Jim

Re: date time variable not properly formatting

jimbarbour — Tue, 10 Mar 2020 20:33:32 GMT

@Kurt_Bremser ,

My statement, as @Kurt_Bremser pointed out, was incorrect. Right idea, poor (and incorrect) application. Please see subsequent response.

~~Since 30 Dec 1899 is before 01 Jan 1960 (the beginning of time in SAS), the value will be negative. I believe you would have to multiply by -1 and apply the 86400 factor before adding, yes?~~

~~The following code:~~

DATA	_NULL_;
	Incident_Datetime	=	43414.950532407405;
	Adjustment			=	'30dec1899'd;
	Adjustment			=	(-1	*	Adjustment);

	Adjusted_Datetime	=	(Incident_Datetime	+	(Adjustment	*	86400));
	Incident_Datetime	=	(Incident_Datetime	+	(21916		*	86400));

	PUTLOG	"NOTE:  "	Incident_Datetime=	DateTime21.;
	PUTLOG	"NOTE-  "	Adjusted_Datetime=	DateTime21.;
RUN;

~~Yields the following results:~~

NOTE:  Incident_Datetime=02JAN2020:12:03:35
       Adjusted_Datetime=02JAN2020:12:03:35

~~Is that what you had in mind?~~

~~Jim~~

Re: date time variable not properly formatting

Tom — Tue, 10 Mar 2020 18:49:37 GMT

When you have mixed cell types in a column in Excel then SAS must import the column as a character variable. If you can fix the Excel file so that only datetime values are in that column. No character values. Then SAS should create the variable as datetime to begin with.

If you cannot then first convert the value to a number. Then add the SAS value for '30DEC1899'd to adjust for different reference dates used by Excel and SAS. (Note that since 1899 is before 1960 the value you are adding is negative).

Then use the DHMS() function to convert it to a datetime value.

datetime = dhms('30DEC1899'd + input(Incident_Date,32.),0,0,0);
format datetime datetime20.;

Then if you want you can use DATEPART() and TIMEPART(), or just leave it as a datetime value.

date = datepart(datetime); 
time = timepart(datetime); 
format date date9. time time8. ;

Re: date time variable not properly formatting

Kurt_Bremser — Tue, 10 Mar 2020 19:37:43 GMT

In Excel, dates and datetimes are stored as counts of days, with the time stored as fraction of a day. So we should add the SAS date of 30dec1899 (since the SAS date for that is a negative value, we subtract from the 43414, giving us a typical value around 20000), and then convert by multiplying by 86400 to get a SAS datetime value. Note that the OP expects a date in October or November 2018, not in January 2020.

Re: date time variable not properly formatting

jimbarbour — Tue, 10 Mar 2020 20:01:17 GMT

@Kurt_Bremser ,

Hmmm. Yes, I think you're right. We should add the negative to go from Excel to SAS.

The code in fact bears you out. The following:

DATA	_NULL_;
	Incident_Datetime	=	43414.950532407405;
	Adjustment			=	'30dec1899'd;

	Incident_Datetime	=	43414.950532407405	+	Adjustment;
	Incident_Datetime	=	(Incident_Datetime	*	86400);

	PUTLOG	"NOTE:  "	Incident_Datetime=	DateTime21.;
RUN;

Yields our 10 Nov 2018 date:

NOTE:  Incident_Datetime=10NOV2018:22:48:46

I had the right idea (adjust for the difference between Excel and SAS), but my application was sloppy -- and incorrect. My apologies.

Jim

Re: date time variable not properly formatting

SASforHealth — Tue, 10 Mar 2020 20:53:26 GMT

hi thank you all for your help.

I have tried code, but was unsuccessful:
Adjustment = '30dec1899'd;
Adjustment = (-1 * Adjustment);
Adjusted_Datetime = (Incident_Datetime + (Adjustment * 86400));

RUN;

Variable changed from:
incident_datetime= 43323.24039351852
1893585723.2

as a reference this variable should be on the same day with a slightly different time datetime2= Adjusted_Datetime= 43323.240231

am i missing something?

Re: date time variable not properly formatting

Kurt_Bremser — Tue, 10 Mar 2020 21:20:07 GMT

The formula needs to be this:

Adjusted_Datetime = (Incident_Datetime + '30dec1899'd) * 86400;

Re: date time variable not properly formatting

SASforHealth — Tue, 10 Mar 2020 21:22:49 GMT

thank you for your help.

would the first step be [Then add the SAS value for '30DEC1899'd to adjust for different reference dates used by Excel and SAS. (Note that since 1899 is before 1960 the value you are adding is negative).] :

SAS_date_time = (Excel_date_time - 21916) * 86400;

Re: date time variable not properly formatting

jimbarbour — Tue, 10 Mar 2020 21:59:42 GMT

@SASforHealth ,

I think that looks good.

SAS_date_time = (Excel_date_time - 21916) * 86400;

Jim

Re: date time variable not properly formatting

SASforHealth — Thu, 12 Mar 2020 18:50:47 GMT

Hi all,

You have all been so helpful, thank you x a million for all the insight you've already provided. I'm checking back in as I am unfortunately still having issues with the coding. I used the below coding and got the below notes.

3276 Adjusted_Datetime = (Incident_Date_time + '30dec1899'd) * 86400;

3277 sasdate = datepart(Adjusted_Datetime);

3278 time = timepart(Adjusted_Datetime);

3279 format sasdate date9. time time8. adjusted_datetime DATEAMPM.;

3280

3281

3282 yrincident=year(sasdate);

3283 monthincident=month(sasdate);

3284 format date mmddyy10. monthincident MONNAME.;

3285

3286 day = weekday(datepart(sasdate));

3287 format day ;

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column). 3276:26

NOTE: Variable date is uninitialized.

NOTE: Invalid numeric data,

Incident_Date_Time='-----------------------------------------------------------------------, at line 3276 column 26.

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

52 at 3276:45 52 at 3277:15 52 at 3278:12 52 at 3282:16 52 at 3283:19

52 at 3286:11 52 at 3286:19 85 at 3290:52 85 at 3291:23 86 at 3292:32

When I print a few rows of observations, the data appears to be true. However, I know it is not converting appropriately since the day reads =6 but in a calendar 01-01-17 was a Sunday.

proc print data=work.abc(obs=250);
var adjusted_Datetime sasdate time yrincident monthincident day Incident_Date_Time;
format day weekdate9. monthincident monthname3.;
run;

Obs Adjusted_Datetime sasdate time yrincident monthincident day Incident_Date_Time1234567

01JAN17:12:00:00 AM	01JAN2017	0:00:00	2017	January	6	42736
01JAN17:12:03:08 AM	01JAN2017	0:03:08	2017	January	6	42736.002175925925
01JAN17:12:10:00 AM	01JAN2017	0:10:00	2017	January	6	42736.006944444445
01JAN17:12:10:00 AM	01JAN2017	0:10:00	2017	January	6	42736.006944444445
01JAN17:12:11:43 AM	01JAN2017	0:11:43	2017	January	6	42736.00813657408
01JAN17:12:19:10 AM	01JAN2017	0:19:10	2017	January	6	42736.01331018518
01JAN17:12:25:47 AM	01JAN2017	0:25:47	2017	January	6	42736.017905092594

Like wise I changed the format to assess if it at least maintained a right pattern, but again it changed:

proc print data=work.abc (obs=250);

var adjusted_Datetime sasdate time yrincident monthincident day Incident_Date_Time;
format day WEEKDAYw. monthincident MONTH.;
run;

Obs Adjusted_Datetime sasdate time yrincident monthincident day Incident_Date_Time1234567

01JAN17:12:00:00 AM	01JAN2017	0:00:00	2017	January	Thursday	42736
01JAN17:12:03:08 AM	01JAN2017	0:03:08	2017	January	Thursday	42736.002175925925
01JAN17:12:10:00 AM	01JAN2017	0:10:00	2017	January	Thursday	42736.006944444445
01JAN17:12:10:00 AM	01JAN2017	0:10:00	2017	January	Thursday	42736.006944444445
01JAN17:12:11:43 AM	01JAN2017	0:11:43	2017	January	Thursday	42736.00813657408
01JAN17:12:19:10 AM	01JAN2017	0:19:10	2017	January	Thursday	42736.01331018518
01JAN17:12:25:47 AM	01JAN2017	0:25:47	2017	January	Thursday	42736.017905092594

Any thoughts or advise would be incredibly appreciated!!!

Re: date time variable not properly formatting

Kurt_Bremser — Fri, 13 Mar 2020 13:02:57 GMT

sasdate is already a date, per this:

3277      sasdate = datepart(Adjusted_Datetime);

so when you do

3286      day = weekday(datepart(sasdate));

and (implicitly) once again divide by 86400, you get a wrong value.

The correct way to deal with an Excel datetime value you received as string is this:

data have;
input excel_dt $30.;
datalines;
43414.950532407405
;

data want;
set have;
sas_dt = (input(excel_dt,best32.) + '30dec1899'd) * 86400;
sas_date = datepart(sas_dt);
format sas_dt e8601dt19. sas_date e8601da10.;
weekday = weekday(sas_date);
run;

proc print data=want noobs;
run;

Result

excel_dt	        sas_dt	                sas_date	weekday
43414.950532407405	2018-11-10T22:48:46	2018-11-10	7

Re: date time variable not properly formatting

Tom — Fri, 13 Mar 2020 13:27:16 GMT

Let's look at some of those messages. Remember when posting lines of date/code/log to use the Insert Code or Insert SAS Code icon on the editor toolbar to get a popup window to paste the lines. That will prevent the forum from trying to reflow the text into paragraphs.

First let's look at these messages:

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).      3276:26

NOTE: Invalid numeric data,
Incident_Date_Time='-----------------------------------------------------------------------, at line 3276 column 26.

The value with the hyphens is what is causing your problem with importing the datetime values from the original spreadsheet. Because at least one of the cells has text (a line of hyphens) instead of datetime values SAS was forced to create a character variable instead of a numeric variable with datetime values.

Use the INPUT function to convert the character string to a number, don't force SAS to guess how to convert it.

Adjusted_Datetime = (input(Incident_Date_time,32.) + '30dec1899'd) * 86400;

If you know those hyphens are there then you could either test for and exclude them.

if not index(Incident_Date_time,'---') then do;
  Adjusted_Datetime = (input(Incident_Date_time,32.) + '30dec1899'd) * 86400;
end;

Or you could add the ?? prefix on the informat and suppress the notes about invalid conversion. But that will suppress all warnings about invalid strings that cannot be converted to a number.

NOTE: Variable date is uninitialized.

You have attached a format to the variable DATE but there is no place in the code where you are assigning it a value.

Every day is 6 (Saturday) because the first of January 1960 is a Saturday. When you try to interpret a SASDATE, which has a number of days, as if it has a number of seconds then it looks like like some time early on the first of January 1960.

Re: date time variable not properly formatting

SASforHealth — Fri, 13 Mar 2020 15:27:01 GMT

I really appreciate your help and insight. That coding worked better, but the day of the week is still incorrect? When I look in a calendar I know 1/1/2017 is a Sunday, but for some reason SAS thinks it is a Saturday? I provided another variable to ground the data. incident-date_time= date of incident. datetime_recordcreate = date of record creation, which can be later than incident date. lot_incidenttoentry is the amount of days datetime_recordcreate is after incident date. I am confused why sometimes they are the same day, other times they are not. it is not following the appropriate date/time pattern? Is there a code to assure leap years are accounted for that may be affecting this?

Adjusted_Datetime = (Incident_Date_time + '30dec1899'd) * 86400;
sasdate = datepart(Adjusted_Datetime);
time = timepart(Adjusted_Datetime);
format sasdate e8601da10. time time8. adjusted_datetime e8601dt19.;

yrincident=year(sasdate);
monthincident=month(sasdate);
format date mmddyy10. monthincident MONNAME.;

day = weekday(sasdate);
format day DOWNAME. ;

Adjusted_RecordCreate = (Datetime_RecordCreate + '30dec1899'd) * 86400;
date_RecordCreate=datepart(Adjusted_RecordCreate);
lot_incidenttoentry=date_RecordCreate-sasDate;

format Adjusted_RecordCreate DATEAMPM. date_RecordCreate mmddyy10. lot_incidenttoentry comma.;

recordcreateday= weekday(date_recordcreate);
format recordcreateday downame.;

Obs Incident_Date_Time Adjusted_Datetime day sasdate time yrincident monthincident Datetime_RecordCreate Adjusted_RecordCreate date_RecordCreate recordcreateday lot_incidenttoentry12345

42736	01JAN17:12:00:00 AM	Saturday	2017-01-01	0:00:00	2017	January	42736.053958	01JAN17:01:17:42 AM	01/01/2017	Saturday	0
42736.002175925925	01JAN17:12:03:08 AM	Saturday	2017-01-01	0:03:08	2017	January	42765.686343	30JAN17:04:28:20 PM	01/30/2017	Sunday	29
42736.006944444445	01JAN17:12:10:00 AM	Saturday	2017-01-01	0:10:00	2017	January	42765.726574	30JAN17:05:26:16 PM	01/30/2017	Sunday	29
42736.006944444445	01JAN17:12:10:00 AM	Saturday	2017-01-01	0:10:00	2017	January	42736.720208	01JAN17:05:17:06 PM	01/01/2017	Saturday	0
42736.00813657408	01JAN17:12:11:43 AM	Saturday	2017-01-01	0:11:43	2017	January	42781.74662	15FEB17:05:55:08 PM	02/15/2017	Tuesday	45

Re: date time variable not properly formatting

Kurt_Bremser — Fri, 13 Mar 2020 15:40:53 GMT

RUN THIS CODE:

data test;
Incident_Date_time = 42736;
Adjusted_Datetime = (Incident_Date_time + '30dec1899'd) * 86400;
sasdate = datepart(Adjusted_Datetime);
day = weekday(sasdate);
format Adjusted_Datetime e8601dt19. sasdate e8601da10.;
run;

and you will CLEARLY see that SAS shows a day of 1 for 2017-01-01.

Make sure that what you look at is the newly created dataset (READ THE LOG!).

Re: date time variable not properly formatting

Tom — Fri, 13 Mar 2020 15:45:10 GMT

You should spend some time reading about SAS formats in general and SAS date/datetime and time formats in particular.

The DOWNAME format works on DATE values. Since the variable you attached it to has values like 1 to 7 those will be considered the dates '02JAN1960'd to '08JAN1960'd . So '01JAN2017'd is a Sunday so that is weekday number 1 which is the date value for '02JAN1960'd which was a Saturday.

3269  data _null_;
3270    do now='01JAN2017'd to '08JAN2017'd ;
3271       dow = weekday(now);
3272       put now = date9.  now = downame. dow= dow= date9. dow = downame. ;
3273    end;
3274  run;

now=01JAN2017 now=Sunday dow=1 dow=02JAN1960 dow=Saturday
now=02JAN2017 now=Monday dow=2 dow=03JAN1960 dow=Sunday
now=03JAN2017 now=Tuesday dow=3 dow=04JAN1960 dow=Monday
now=04JAN2017 now=Wednesday dow=4 dow=05JAN1960 dow=Tuesday
now=05JAN2017 now=Thursday dow=5 dow=06JAN1960 dow=Wednesday
now=06JAN2017 now=Friday dow=6 dow=07JAN1960 dow=Thursday
now=07JAN2017 now=Saturday dow=7 dow=08JAN1960 dow=Friday
now=08JAN2017 now=Sunday dow=1 dow=02JAN1960 dow=Saturday

Re: date time variable not properly formatting

Kurt_Bremser — Fri, 13 Mar 2020 15:56:34 GMT

You should really start to read the documentation. DOWNAME. is a date format, and needs to be applied to a SAS date value, not the result of the WEEKDAY() function. My dataset test shows that weekday() returns a 1 for 2017-01-01, and 1 is Sunday. If you apply the format to a date value of 1, you get the weekday of 1960-01-02.

Re: date time variable not properly formatting

SASforHealth — Fri, 13 Mar 2020 16:36:44 GMT

That was very helpful thank you so much for that explanation!