topic Re: Infile problem in SAS Programming

Infile problem

Ody — Fri, 08 Jul 2016 14:47:37 GMT

Hello all,

I have a pipe delimited text file that's been giving me a headache for the last day or so. I cant seem to get it to import properly and can't determine why. I've tried with data step/infile but had no luck. Then tried using enterprise guide but it's reading the file as 1 long line of data. I went back to infile but am stuck. Appreciate any help. Many thanks.

Example file data:

"CUE.AFO.Ao"|"CUE.ACT.ID"|"CUE.ANT.Date"|"JLA-666" "24441626"|"htrwsen"|"01/19/16"|"10:07:40" "24451626"|"grrflos"|"02/23/16"|"16:36:30" "24471626"|"rvmzzen"|"01/19/16"|"16:41:09" "24481626"|"bbgalng"|"02/25/16"|"17:47:07" "24501626"|"jyghars"|"01/14/16"|"15:41:14" "24511626"|"jhtgars"|"02/04/16"|"10:48:16" "24531626"|"lkazall"|"01/15/16"|"11:05:22" "24551626"|"qwsdrig"|"04/25/16"|"13:37:32" "24561626"|"hhkleat"|"01/14/16"|"12:48:03"

Re: Infile problem

ballardw — Fri, 08 Jul 2016 15:00:42 GMT

Then tried using enterprise guide but it's reading the file as 1 long line of data

This is often a clue that the file originated in a different operating system than you are using and the end of line character(s) are different.

My first guess is that you are using Windows and the file originated as some flavor of Unix. Often the solution is to ad the TERMSTR= option to your infile statement.

If the file originated in UNIX then use TERMSTR=LF, if from MAC or Apple TERMSTR=CR

Since the Windows default end of line is a CR(carriage return)LF(linefeed) when read by the other OS you often get slightly garbled data as there is an odd charcter in one position.

Re: Infile problem

Ody — Fri, 08 Jul 2016 16:35:10 GMT

Thanks for the reply.

I believe this file did originate from a different OS. I have other files I've been able to successfully import from this environment. In fact my last post was related to this. To solve that particular problem I used _infile_ = compress(_infile_,' ',"kw"); to remove the special characters that were causing me issues.

This file looks almost identical to those files except its not doing what I want it to do, lol. I suspect it has to do with all of the quotes and the way the datalines trail each other.

EDIT: I tried the TERMSTR=LF and that didn't help.

Re: Infile problem

rogerjdeangelis — Fri, 08 Jul 2016 19:54:13 GMT

I think this will display the hex codes for the end of record control characters

filename badchr "d:/txt/badchr.txt" lrecl=80 recfm=f;

proc fslist file=badchr;
;run;quit;

Works best in the old text editor with the command line turned on

* on the FSLIST command line type hex on;nums on;

*You should see the end of file characters;

*Look for hex codes 0A 0D 0c at the end of the records;

Re: Infile problem

Ody — Sat, 09 Jul 2016 00:28:57 GMT

After spending all day toiling over this I finally figured out my problem. I feel kind of dumb now. 🙂

I listed a few records of data to the log:

data _null_; 			
infile procfile; 		
input; 				
list; 					
if _n_ > 5 then 		
stop; 				
run;

Found the hex value that was giving me anxiety '0D'. Realized it was a carriage return and added TERMSTR = CR to my infile which solved the problem.

    173  |"02/10/16"|"15:16:12"."25921626"|"ceeedta"|"01/19/16"|"11:24:24"."25931626"|"shwedul"
    ZONE 72332332332723333333320233333333272667667627233233233272333333332023333333327276666762
    NUMR C202F10F162C215A16A122D2162625922C2312D9412C201F19F162C211A24A242D2162625932C2385E15C2

I guess you both, rogerjdeangelis and ballardw, were pointing me in the right direction. It just took forever for me to figure it out, lol. I was determined to conquer this before the weekend. Thanks.