date time variable not properly formatting

[SASforHealth]

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);

[jimbarbour]

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

[Kurt_Bremser]

Add

'30dec1899'd

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

[jimbarbour]

@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

[Kurt_Bremser]

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.

[jimbarbour]

@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

[SASforHealth]

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?

[Kurt_Bremser]

The formula needs to be this:

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

[jimbarbour]

@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

[Tom]

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. ;

[SASforHealth]

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;

[jimbarbour]

@SASforHealth ,

I think that looks good.  

SAS_date_time = (Excel_date_time - 21916) * 86400;

Jim

[SASforHealth]

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!!!

[Kurt_Bremser]

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

[SASforHealth]

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