date time variable not properly formatting

[Kurt_Bremser]

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

[SASforHealth]

thank you!

[Tom]

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

[SASforHealth]

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

[Kurt_Bremser]

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.

[SASforHealth]

thank you, this is helpful.

[Tom]

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.

Next:

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.

[SASforHealth]

thank you so much! you have taught me a lot, I really appreciate it!

[SASforHealth]

	Adjusted_Datetime = (input(Incident_Date_time,32.) + '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);


	day = weekday(sasdate);  

	*code lot from incident to data entry (could also consider looking at Datetime_RecordModify);
	Adjusted_RecordCreate = (input(Datetime_RecordCreate,32.) + '30dec1899'd) * 86400;
	date_RecordCreate=datepart(Adjusted_RecordCreate);
	lot_incidenttoentry=date_RecordCreate-sasDate;
	format Adjusted_RecordCreate e8601dt19. date_RecordCreate mmddyy10. lot_incidenttoentry comma.;

	recordcreateday= weekday(date_recordcreate);

Upon entering the above code, the table appears great.

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

42736	2017-01-01T00:00:00	1	2017-01-01	0:00:00	2017	1	42736.053958	2017-01-01T01:17:42	01/01/2017	1	0
42736.002175925925	2017-01-01T00:03:08	1	2017-01-01	0:03:08	2017	1	42765.686343	2017-01-30T16:28:20	01/30/2017	2	29
42736.006944444445	2017-01-01T00:10:00	1	2017-01-01	0:10:00	2017	1	42765.726574	2017-01-30T17:26:16	01/30/2017	2	29
42736.006944444445	2017-01-01T00:10:00	1	2017-01-01	0:10:00	2017	1	42736.720208	2017-01-01T17:17:06	01/01/2017	1	0

However, I am getting the below notes.

912      Adjusted_Datetime = (input(Incident_Date_time,32.) + '30dec1899'd) * 86400;
913      sasdate = datepart(Adjusted_Datetime);
914      time = timepart(Adjusted_Datetime);
915      format sasdate e8601da10. time time8. adjusted_datetime e8601dt19.;
916
917      yrincident=year(sasdate);
918      monthincident=month(sasdate);
919
920
921      day = weekday(sasdate);
922
923      *code lot from incident to data entry (could also consider looking at
923! Datetime_RecordModify);
924      Adjusted_RecordCreate = (input(Datetime_RecordCreate,32.) + '30dec1899'd) * 86400;
925      date_RecordCreate=datepart(Adjusted_RecordCreate);
926      lot_incidenttoentry=date_RecordCreate-sasDate;
927      format Adjusted_RecordCreate e8601dt19. date_RecordCreate mmddyy10. lot_incidenttoentry
927! comma.;
928
929      recordcreateday= weekday(date_recordcreate);




NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      924:36
NOTE: Invalid argument to function INPUT at line 912 column 26.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
Incident_Date_Time=\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ Incident_Number=

Adjusted_Datetime=. sasdate=. time=. yrincident=. monthincident=. day=. Adjusted_RecordCreate=.
date_RecordCreate=. lot_incidenttoentry=. recordcreateday=.   _ERROR_=1 _N_=543249
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 912:56   52 at 913:15   52 at 914:12   52 at 917:16   52 at 918:19   52 at 921:11
      85 at 924:63   85 at 925:23   86 at 926:42   85 at 929:22
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      52 at 912:26

[Kurt_Bremser]

You have lots of invalid values in incident_date_time; you will have to do some cleaning during the import, or check for certain invalid values here to avoid this message.