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;
@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.
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|
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: