INFILE Statement for external file with delimiters

[Anuz]

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 

[SASJedi]

The DSD modifier will resolve this problem.

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

[FreelanceReinh]

---

@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=.

[Anuz · Accepted solution]

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 

[Anuz]

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

 

[Tom]

---

@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.  

[Anuz]

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