Remove unnecessary delimiters when reading in text file

[A_Swoosh]

Hello, I'm attempting to read in raw text files. The data is in text file format with pipe delimiters and the below is a subset example with not all variables listed:

Category|ID|PIN|First_Name|Middle_Name|Last_Name|BusName|Business_Ind|

I encounter situations in the middle of my data as follows: 

SP220|12345|123456789|John|D|Doe|This is | A | Fake Business|N|

From above, I can see that 'This is A Fake Business' should be the business name but since the delimiter is the same format as the rest, it keeps moving on to the next variable.

Without manually making these changes, is there a way to correct this issue in SAS? Also, the next variable is likely to be a single letter (e.g. "N") after business name if that is any help. 

My code is as follows:

	    data raw_&file.0    ;
		    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
		    infile  "&prov.\&provfile." delimiter = &dlm. dsd  missover  lrecl=32767 firstobs=1 ;
		       informat v1 -v200 $256. ;
		       format v1 -v200 $256. ;
		    input v1 -v200 @;
		run;

[Reeza]

Typically if the delimiter is also embedded in text the general protocol is to quote the entire field. Is that not happening in your file? Can you post some records from the exact text file - do not use Excel as an intermediary - it will not share the data correctly.

[A_Swoosh]

Here are some lines from my text file:

Category|ID|PIN|First_Name|Middle_Name|Last_Name|BusName|Business_Ind
SP220|686|1003|John|R|Smith|ABC-Health Hospital|N|
SK220|786|1633|Steven|R|Smith|Health Clinic R US|N|
SV220|886|1653|Adam|R|Smith|HTP Business Depot|N|
SP220|12345|123456789|John|D|Doe|This is | A | Fake Business|N|
SE220|986|1513|Jane|R|Smith|Non-profit Center Campus|N|
SR220|286|11113|Jeremy|R|Smith|Heart & Health Center|N|

[data_null__ · Accepted solution]

---

@A_Swoosh wrote:

Here are some lines from my text file:

Category|ID|PIN|First_Name|Middle_Name|Last_Name|BusName|Business_Ind
SP220|686|1003|John|R|Smith|ABC-Health Hospital|N
SK220|786|1633|Steven|R|Smith|Health Clinic R US|N
SV220|886|1653|Adam|R|Smith|HTP Business Depot|N
SP220|12345|123456789|John|D|Doe|This is | A | Fake Business|N
SE220|986|1513|Jane|R|Smith|Non-profit Center Campus|N
SR220|286|11113|Jeremy|R|Smith|Heart & Health Center|N

---

INFILE magic.

100  data test;
101     infile cards dsd dlm='|';
102     input @;
103     _infile_ = transtrn(_infile_,' | ',' ');
104     input (v1-v8) (:$64.);
105     list;
106     cards;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
107        Category|ID|PIN|First_Name|Middle_Name|Last_Name|BusName|Business_Ind
108        SP220|686|1003|John|R|Smith|ABC-Health Hospital|N
109        SK220|786|1633|Steven|R|Smith|Health Clinic R US|N
110        SV220|886|1653|Adam|R|Smith|HTP Business Depot|N
111        SP220|12345|123456789|John|D|Doe|This is A Fake Business|N 76
112        SE220|986|1513|Jane|R|Smith|Non-profit Center Campus|N 76
113        SR220|286|11113|Jeremy|R|Smith|Heart & Health Center|N 76
NOTE: The data set WORK.TEST has 7 observations and 8 variables.