Need help with delimiter for text file

[procsql]

Hi wonderful SAS community!

I am trying to create a data step view for a large text file. The text file has the following delimiter    "|"   (i.e. a pipe surrounded by double quotes). Sample data is below

NAME "|" HEIGHT "|" TITLE

TEVIN "|" 6'4 "|" FORWARD

When writing the infile statement, what should I put for the delimiter (DLM=) I've tried DLM='"|"" with no DSD and am getting funny results.

Any help you can provide would be much appreciated!

Thank you!

[novinosrin]

Hi @procsql 

data read_text;
if _n_=1 then k='"|"' ;
infile datalines  dlm=k;
input NAME $ HEIGHT $  TITLE : $10.;
cards;
TEVIN "|" 6'4 "|" FORWARD
;

[novinosrin]

or just

data read_text;
infile datalines  dlm='"|"';
input NAME $ HEIGHT $  TITLE : $10.;
cards;
TEVIN "|" 6'4 "|" FORWARD
TEVIN "|" 6'4 "|" FORWARD
TEVIN "|" 6'4 "|" FORWARD
;

[Astounding · Accepted solution]

It would be safer to switch to dlmstr= instead of dlm=.  Not knowing what might appear in your data, dlm= might have a flaw.  It would treat any pipe as a delimiter, whether or not it is surrounded by quotes.  So ....

infile source dsd dlmstr='"|"';

[procsql]

Thank you (and everyone else) who contributed to answering this question. You all are amazing!

I made one slight modification. Just used dlmstr='"|"' and removed the dsd part.  It worked perfectly. 

[Tom]

Did you check if it is possible to have them create a more reasonable file?  It really looks like perhaps they did quite get the concept of a delimited file.

[ballardw]

If the data is not terribly sensitive then use a text editor to copy 10 or 15 lines or so and paste into a code box (important) opened using the forum's {I} icon.

If some of the data is sensitive then replace it with values like "XXXXXXX" and "YYYYYYY" for character values.

Did this project come with anything that described the data? Such as which fields should be numeric and character and what format/ length of values are supposed to be in the file? That is the first place to start.

By the way your example dlm has unbalanced quotes:

DLM ='"|"" 

You would want to use

DLMSTR = '"|"'

to use the whole value as a single delimiter.