topic Re: Need help with delimiter for text file in New SAS User

Need help with delimiter for text file

procsql — Wed, 27 Mar 2019 21:59:37 GMT

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!

Re: Need help with delimiter for text file

novinosrin — Wed, 27 Mar 2019 22:07:19 GMT

Hi @procsql

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

Re: Need help with delimiter for text file

novinosrin — Wed, 27 Mar 2019 22:10:38 GMT

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
;

Re: Need help with delimiter for text file

Astounding — Wed, 27 Mar 2019 22:58:05 GMT

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='"|"';

Re: Need help with delimiter for text file

Tom — Thu, 28 Mar 2019 01:25:59 GMT

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.

Re: Need help with delimiter for text file

ballardw — Fri, 29 Mar 2019 22:02:36 GMT

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.

Re: Need help with delimiter for text file

procsql — Thu, 28 Mar 2019 23:44:54 GMT

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.