DATA Step, Macro, Functions and more

Reading a CSV file; datetime variable returning missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Reading a CSV file; datetime variable returning missing values

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;

Accepted Solutions
Solution
‎12-24-2015 11:24 AM
Super User
Posts: 9,687

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

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,131

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

Could you try to change the informat used for reading the datetime like below , i used the colon (Smiley Happy 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;

 

Thanks,
Jag
Solution
‎12-24-2015 11:24 AM
Super User
Posts: 9,687

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

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;
Occasional Contributor
Posts: 7

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 215 views
  • 4 likes
  • 3 in conversation