topic Re: Reading a CSV file; datetime variable returning missing values in SAS Programming

Reading a CSV file; datetime variable returning missing values

old_man_hank — Thu, 24 Dec 2015 03:42:00 GMT

Good evening,

The code below is producing missing values for all values of the Rec_Date variable. This is running on 9.2 in UNIX.

Sample data appears as follows:

"First ID","Second ID","Name","Rec Date"
"121","012","Martin","2015/12/22 00:00:00"
"1956","056","Breida","2015/11/22 00:00:00"

Can someone help me understand what I'm missing?

Thanks!

data control;

length
	first_ID		$20
	second_ID	$20
	name	        $50;
infile control dsd missover firstobs=2;
input
	first_ID		$
	second_ID	$
	name	         $
	Rec_Date	         anydtdtm.;
run;

Re: Reading a CSV file; datetime variable returning missing values

Jagadishkatam — Thu, 24 Dec 2015 04:05:13 GMT

Could you try to change the informat used for reading the datetime like below , i used the colon (:) and the informat to anydtdtm20. .

data control;

length
	first_ID		$20
	second_ID	$20
	name	        $50;
infile control dsd missover firstobs=2;
input
	first_ID		$
	second_ID	$
	name	         $
	Rec_Date	 :        anydtdtm20.;
run;

Re: Reading a CSV file; datetime variable returning missing values

Ksharp — Thu, 24 Dec 2015 05:15:50 GMT

Maybe the problem is that csv file is created under Windows, while you are importing it via Unix . They have different terminator character of line .

data control;
infile '/folders/myfolders/temp.txt' dsd truncover firstobs=2 termstr=crlf;
input
	first_ID	: $20.
	second_ID	: $20.
	name	    : $50.
	Rec_Date	: anydtdtm.;
run;

Re: Reading a CSV file; datetime variable returning missing values

old_man_hank — Thu, 24 Dec 2015 16:27:38 GMT

Thanks Ksharp! That is exactly what the issue was. Apparently UNIX uses a "LF" to terminate the line while files created in Windows have the "CRLF". Once I specified that the terminator characater is "CRLF", the file read the date without issue.

Text file common problems