topic Re: INFILE Statement for external file with delimiters in SAS Programming

INFILE Statement for external file with delimiters

Anuz — Sun, 10 Mar 2024 17:51:13 GMT

Hi All,

I need some help with my INFILE statement.

I have an external file that has more than 500 fields.

If the file has delimiter as # the below code works absolutely fine.

INFILE '/file_location/customer.txt'
FIRSTOBS=2
LRECL=32767
ENCODING="LATIN9"
TERMSTR=CRLF
DLM="#"
MISSOVER
DSD
;

But my file has |# as the delimiter

INFILE '/file_location/customer.txt'
FIRSTOBS=2
LRECL=32767
ENCODING="LATIN9"
TERMSTR=CRLF
DLM="|#"
MISSOVER
;

This seemed to work ok.

but if there is no value between the delimiters it screws the alignment of the data to the columns

I have created and attached a test file that I used.

Below is the code i used

DATA TEST;
LENGTH
cust_id          $ 500
caseId           $ 500
website         $ 500
;
LABEL
cust_id = "CUST ID "
caseId = "CASE"
website = "website"
;
FORMAT
cust_id          $CHAR500.
caseId           $CHAR500.
website       $CHAR500.
;
INFORMAT
cust_id          $CHAR500.
caseId           $CHAR500.
website       $CHAR500.
;
INFILE '/file_locn/test.txt'
FIRSTOBS=2
LRECL=32767
ENCODING="LATIN9"
TERMSTR=CRLF
DLM="|#"
MISSOVER

;
INPUT
cust_id          : $CHAR500.
caseId           : $CHAR500.
website : $CHAR500.
;
RUN;

Can you please tell me what I need to correct in the code to load the file correctly with blank values when there are no values. Thank you

Re: INFILE Statement for external file with delimiters

SASJedi — Sun, 10 Mar 2024 18:10:56 GMT

The DSD modifier will resolve this problem.

INFILE '/file_location/customer.txt' FIRSTOBS=2 LRECL=32767 ENCODING="LATIN9" TERMSTR=CRLF DLM="|#" DSD MISSOVER;

Re: INFILE Statement for external file with delimiters

FreelanceReinh — Sun, 10 Mar 2024 18:19:02 GMT

@SASJedi wrote:

The DSD modifier will resolve this problem.
INFILE '/file_location/customer.txt' FIRSTOBS=2 LRECL=32767 ENCODING="LATIN9" TERMSTR=CRLF DLM="|#" DSD MISSOVER;

... if, in addition, DLM= is replaced with DLMSTR=.

Re: INFILE Statement for external file with delimiters

Anuz — Sun, 10 Mar 2024 19:01:08 GMT

I had tried that . DSD did not help.

If i used the below

	INFILE 'FILE_LOCN/TEST_FILES/test.txt'
		FIRSTOBS=2
		LRECL=32767
		ENCODING="LATIN9"
		TERMSTR=CRLF
		DLM="|#"
		DSD
		MISSOVER

Below is the output. it puts a blank into case id

Re: INFILE Statement for external file with delimiters

Anuz — Sun, 10 Mar 2024 19:07:02 GMT

That seems to have worked

Used the below

INFILE 'File_Locn/test.txt'
FIRSTOBS=2
LRECL=32767
ENCODING="LATIN9"
TERMSTR=CRLF
DLMSTR="|#"
DSD
MISSOVER

That has loaded the data correctly. The key was DLMSTR. I was not aware of the same.

if DLM with DSD is used it does not resolve the issue.

DLMSTR with DSD works.. I shall now test with the real files and fingers crossed should be ok .Thank you

Re: INFILE Statement for external file with delimiters

Tom — Sun, 10 Mar 2024 22:11:23 GMT

@Anuz wrote:

I had tried that . DSD did not help.

If i used the below
	INFILE 'FILE_LOCN/TEST_FILES/test.txt'
		FIRSTOBS=2
		LRECL=32767
		ENCODING="LATIN9"
		TERMSTR=CRLF
		DLM="|#"
		DSD
		MISSOVER
Below is the output. it puts a blank into case id

It did what you asked it to do. Since both | and # are possible delimiter characters when they are next to each other that means there is an empty value. You could compensate by reading those into a dummy variable.

Example:

filename test temp;
options parmcards=test;
parmcards;
cust_id|#caseId|#website
B132|#1453|#abcd@co.uk
B132|#null|#abcd@co.uk
B132|#|#abcd@co.uk
;


data test1;
  infile test dsd truncover dlm='|#' firstobs=2 ;
  input cust_id $ dummy caseid $ dummy website :$100.;
  drop dummy;
run;

data test2;
  infile test dsd truncover dlmstr='|#' firstobs=2 ;
  input cust_id $ caseid $ website :$100.;
run;

proc compare data=test1 compare=test2;
run;

PS There is no need to use the ANCIENT option MISSOVER. Use the MODERN (probably 30 years old) TRUNCOVER option instead. Doesn't matter as long as you only use LIST MODE input, but if you slip into using FORMATTED mode or COLUMN mode input then you have told SAS to IGNORE text at the end of the line that is shorter than the informat being used.

Re: INFILE Statement for external file with delimiters

Anuz — Mon, 11 Mar 2024 08:13:00 GMT

Thank you Tom .. Appreciate the detail that you have shared. Really helpful.