topic Re: pipe delimited file in SAS Procedures

pipe delimited file

akilees — Tue, 15 Oct 2013 18:35:36 GMT

Hi!

I'm trying to read a pipe delimited file. And for some character variable, the value contains one or more blanks. Although I specified that delimiter='|' in the infile, it seems like sas still treat blank as a secondary delimiter. The result is it successfully delimit each variable, but for each value, sas cut off the part after the first blank. How can I fix it?

Thanks

Akilees

Re: pipe delimited file

ballardw — Tue, 15 Oct 2013 19:17:18 GMT

The option DSD on an infile statement usually works for this. The full code of what you're attempting would help.

It might also be that your character variables are defaulting to 8 characters and need to be longer.

Re: pipe delimited file

akilees — Tue, 15 Oct 2013 19:30:10 GMT

Thank you! Here is the code for reading the variables. Right, I should specify the length, but I don't know how long I should do, so I just estimate the longest possible value?

data have;

infile '' lrecl=32767 firstobs=2 dlm='|';
input level1 $ level2_code $ level2 $;

run;

Re: pipe delimited file

akilees — Tue, 15 Oct 2013 20:05:45 GMT

But I just realized that the length has been assigned in lrecl=32767, right? why is it still truncating?

Re: pipe delimited file

PGStats — Tue, 15 Oct 2013 20:18:06 GMT

Try

data have;

length level1 level2_code level2 $32;

infile '' lrecl=32767 firstobs=2 dlm='|' missover;
input level1 level2_code level2;

run;

Re: pipe delimited file

snoopy369 — Tue, 15 Oct 2013 20:59:41 GMT

akilees wrote:

Thank you! Here is the code for reading the variables. Right, I should specify the length, but I don't know how long I should do, so I just estimate the longest possible value?

data have;
     infile '' lrecl=32767 firstobs=2 dlm='|';
     input level1 $ level2_code $ level2 $;
run;

lrecl is only defining the length of the total record. You still need to define your character variables' length. For example:

data have;

infile a lrecl=32767 firstobs=2 dlm='|';

input level1 :$15. level2_code :$3. level2 :$15.;

run;

(note the colons)

data have;

infile a lrecl=32767 firstobs=2 dlm='|';

informat level1 level2 $15. level2_code $3.;

input level1 $ level2_code $ level2 $;

run;

Or use length or format similarly. Length is somewhat better if you're just defining lengths and don't have informats for your variables (all plain characters); informat is somewhat more common if you do have some informats (date variables etc.) and matches what you see from PROC IMPORT.

One trick you can use is to PROC IMPORT, see what the proc import decides for lengths, and borrow that (look at your log).

Re: pipe delimited file

akilees — Mon, 21 Oct 2013 19:41:31 GMT

Thank you Snoopy! I got the character length from the import procedure.

Re: pipe delimited file

akilees — Mon, 21 Oct 2013 19:58:00 GMT

BTW, if import can do the job, what is data infile used for? I guess both of them can read raw date files and specify the delimiter. I tried to search the difference between them but didn't get a clear idea.

Re: pipe delimited file

snoopy369 — Mon, 21 Oct 2013 20:01:52 GMT

Data step read gives you more control than PROC IMPORT; so if PROC IMPORT guesses wrong, or doesn't give you the result you need in terms of formats/informats, you should use data step read. I typically only use PROC IMPORT as a helper (to give me a starting point) in my production code, as I worry PROC IMPORT may yield inconsistent results; but if you're okay with that possibility (if this is a one-time run, for example, or minor inconsistencies with length of text variables and such aren't important) and you're not doing complicated things with formats, you can just use PROC IMPORT.

Re: pipe delimited file

akilees — Mon, 21 Oct 2013 21:10:01 GMT

Thanks for sharing!