Date Time format issues

[khoffmann]

Hello,

I am having issues formatting my infile date time.  I cannot get it to import correctly.  Eventually I would like to use Dif() to calculate the differences between this current load, and another data load I will do.  But first I have to get this data loaded! 

Here is what I have for the code currently:

Data have;
        infile 'c:\path'
          delimiter = ',' ;
informat FieldA$6. ;
informat FieldB$10. ;
informat Created mdyhm. ;
informat Updated mdyhm. ;
   format FieldA$6. ;
   format FieldB$10. ;
   format Created datetime. ;
   format Updated datetime. ;
      input
         FieldA $

         FieldB $

         Created

         Updated
;
Run;

The date fields are created and updated and some sample code as to what is looks like in the CSV file are (same for both):

10/20/2017 7:00

10/20/2017 7:00

10/19/2017 14:09

10/19/2017 11:47

MM/DD/YYYY  HH:MM

I would like it within SAS entirely the same--I do not care about the order of the mm/dd/yyyy just as long as the date, and time are in there.

Thanks for the help in advance! 

[ballardw]

Is the informat Anydtdtm. available to you? That will make a guess as to the format of the datetime.

I have to say I have never seen mdyhm. as an informat.

[Patrick]

@khoffmann

The following should work

options datestyle=mdy;
data sample;
  infile datalines truncover dsd dlm=' ';
  input mydttm  anydtdtm16.;
  format mydttm datetime21.;
  datalines;
10/20/2017:7:00
10/20/2017 7:00
10/19/2017 14:09
10/19/2017 11:47
;
run;

[khoffmann]

Thank you all for the quick replies! 

@Patrick I tried what you suggested and something is populating now! However it is not coming in correctly (I attached a picture of the result).  The Created date should always be populated per the CSV, as well as the Updated.  Due Date will be null for now.  I had to format prior to the input due to my other fields that were becoming truncated. 

Data Date.Have;
infile 'U:\SAS\table\data.csv'

truncover dsd
delimiter = ',' ;

format Type $6. ;
format Key $10. ;
format ID $10. ;
FORMAT PID $10.;
format Summ $45. ;
format Assigned $14. ;
format Reporter $6. ;
format Priority $6. ;
format Status $15. ;
format Resolution $6. ;
format Created datetime21. ;
format Updated datetime21. ;
format Due_Date $6. ;

input
Type $
Key $
ID $
PID $
Summ $
Assigned $
Reporter $
Priority $
Status $
Resolution $
Created anydtdtm16.
Updated anydtdtm16.
Due_Date $
;
Run;

[khoffmann]

Here is also what the data looks like in the CSV see attached.  @Patrick 

Thank you again for your help! Date/times are always tricky--I really appreciate it!

[ballardw]

---

@khoffmann wrote:

Here is also what the data looks like in the CSV see attached.  @Patrick 

Thank you again for your help! Date/times are always tricky--I really appreciate it!

---

Your Doc20.pdf is NOT how a csv looks. That is what a spreadsheet program makes a csv file look like after interpretation by the spreadsheet.

Paste a few lines of the CSV file after copying from the file opened with a text editor such as Notepad or even the SAS program editor into a code box opened with the forum {I] menu icon.

I strongly suggest moving the informat information into an informat statement not on the input as there are a number of things that happen regarding how/what is read when the informat is on an input statement.

Informat Created Updated anydtdtm.;

before the input statement.

[khoffmann]

To Do,D290,15557,,WS Live 10/20/17,jdoe,admin,,Closed,Done,10/20/2017 7:00,10/20/2017 9:07,
To Do,D289,14456,,ACS 10/20/17,jdoe,admin,,Closed,Done,10/20/2017 7:00,10/20/2017 7:22,

 
Data data.have;
	infile 'U:\SAS\table\data.csv' truncover dsd
	delimiter = ','  ;

	
		informat Type $6. ;
		informat Key $10. ;
		informat ID $10. ;
		informat PID $10.;
		informat Summ $45. ;
		informat Assigned $14. ;
		informat Reporter $6. ;
		informat Priority $6. ;
		informat Status $15. ;
		informat Resolution $6. ;
		informat Created anydtdtm16. ;
		informat Updated anydtdtm16. ;
		informat Due_Date $6. ;

			format Type $6. ;
			format Key $10. ;
			format ID $10. ;
			FORMAT PID $10.;
			format Summ $45. ;
			format Assigned $14. ;
			format Reporter $6. ;
			format Priority $6. ;
			format Status $15. ;
			format Resolution $6. ;
			format Created datetime21. ;
			format Updated datetime21. ;
			format Due_Date $6. ;
	input
		Type $
		Key $
		ID $
		PID $
		Summ $
		Assigned $ 
		Reporter $
		Priority $
		Status $ 
		Resolution $	
		Created anydtdtm16.
		Updated anydtdtm16.
		Due_Date $
	;
Run;

@ballardw Is the above enough? I can paste a few more lines if necessary. Thank you again for the help!

I changed it back to what I originally had, plus the datetime21. and anydtdtm16. you and Patrick had suggested however same results the data is not being populated correctly/nor fully.  

[ballardw]

---

@khoffmann wrote:

To Do,D290,15557,,WS Live 10/20/17,jdoe,admin,,Closed,Done,10/20/2017 7:00,10/20/2017 9:07,
To Do,D289,14456,,ACS 10/20/17,jdoe,admin,,Closed,Done,10/20/2017 7:00,10/20/2017 7:22,

@ballardw Is the above enough? I can paste a few more lines if necessary. Thank you again for the help!

I changed it back to what I originally had, plus the datetime21. and anydtdtm16. you and Patrick had suggested however same results the data is not being populated correctly/nor fully.  

---

You should provide some of the lines that are "not being populated correctly".

Also the code may perform differently if you use something more like this (extra informat, format statements removed for brevity)

You might get "incomplete" values by truncation with anydtdtm16 if the actual value exceeds 16 characters. And when the format length is specified on an input statement and the variable is short then the data is treated as incomplete (attempting to read 16 characters but only 15 found+ the , which is not valid in a date in that form or position). Informats on an input statement are more intended for use with fixed width text not delimited text.

		informat Created anydtdtm. ;
		informat Updated anydtdtm. ;
		informat Due_Date $6. ;
	input
		Type $
		Key $
		ID $
		PID $
		Summ $
		Assigned $ 
		Reporter $
		Priority $
		Status $ 
		Resolution $	
		Created 
		Updated
		Due_Date $
	;

If those two lines are problems note that this does appear to read them correctly:

Data have;
	infile datalines truncover dsd
	delimiter = ','  ;

	
		informat Type $6. ;
		informat Key $10. ;
		informat ID $10. ;
		informat PID $10.;
		informat Summ $45. ;
		informat Assigned $14. ;
		informat Reporter $6. ;
		informat Priority $6. ;
		informat Status $15. ;
		informat Resolution $6. ;
		informat Created anydtdtm. ;
		informat Updated anydtdtm. ;
		informat Due_Date $6. ;
      format  Created  Updated datetime21.;
	input
		Type $
		Key $
		ID $
		PID $
		Summ $
		Assigned $ 
		Reporter $
		Priority $
		Status $ 
		Resolution $	
		Created 
		Updated
		Due_Date $
	;
datalines;
To Do,D290,15557,,WS Live 10/20/17,jdoe,admin,,Closed,Done,10/20/2017 7:00,10/20/2017 9:07,
To Do,D289,14456,,ACS 10/20/17,jdoe,admin,,Closed,Done,10/20/2017 7:00,10/20/2017 7:22,
;
run;

[khoffmann]

 
Data data.have
	infile 'U:\SAS\table\data.csv' truncover dsd
	delimiter = ','  ;

	
		informat Type $6. ;
		informat Key $10. ;
		informat Id $10. ;
		informat PID $10.;
		informat Summ $45. ;
		informat Assigned $14. ;
		informat Reporter $6. ;
		informat Priority $6. ;
		informat Status $15. ;
		informat Resolution $6. ;
		informat Created anydtdtm. ;
		informat Updated anydtdtm. ;
		informat Due_Date $6. ;

		
	input
		Type $
		Key $
		Id $
		PID $
		Summ $
		Assigned $ 
		Reporter $
		Priority $
		Status $ 
		Resolution $	
		Created 
		Updated 
		Due_Date $
	;
Run;<code></code>

 Thank you for the detail @ballardw! This helps me not only just do it, but understand--so thank you for that. I really appreciate the time you are taking to help me.  I modified as suggested, and all the fields are populated--however they seem to be numeric values (attached screenshot of SAS table).  Am I missing something? Can I translate that back to the date/time using a custom format? 

[ballardw · Accepted solution]

Just add the format to display the variables as datetimes as you had before.

Dates, times and datetimes in SAS are numeric variables with, if created properly, values that assigned proper formats appear as humans like to see them and the various SAS functions will return (usually) expected date or time parts and intervals. If you have dates later than 31Dec9999 or prior to 1581 expect problems or odd behaviors on occasion.

[khoffmann]

Thank you very much @ballardw! It worked! I cannot thank you enough.