topic Re: Converting Text Delimited file into a SAS Dataset in SAS Programming

Converting Text Delimited file into a SAS Dataset

marief58 — Mon, 17 Nov 2008 18:21:05 GMT

I have a SAS job in which I am trying to take a text delimited dataset and converting it into a regular dataset -- getting ride of the "" and ;. And lining up each column.

Following is the code I am currently using, but since it is a text delimited file, it does not line up appropriately or pull in the correct fields

DATA EMPS;
INFILE EJS;
INPUT
@ 2 FOLDER $4.
@ 48 SERIAL $6.
@ 56 DATE $8.
@ 66 DOCNAME $20.;

IF FOLDER = 'APPL';
OPTIONS NOCENTER MISSING=' ';
DATA EMPS; SET EMPS;
RUN;
PROC SORT; BY SERIAL;
QUIT;
DATA _NULL_;
SET EMPS;
FILE OUT NOPRINT NOTITLE;
PUT @1 SERIAL 6.
@8 FOLDER 4.
@12 DATE 8.
@20 DOCNAME 20.;
RUN;

The data in the text delimited file looks like:

"APPL";"ASCENT1";"A1001001A06K11B20419A97524";"999999";20060516;"Matters of Impo"
"APPL";"ASCENT1";"A1001001A06K11B20421I87207";"999999";20060516;"Matters of Impo"

and when I run the code above, it looks like:

;"7339 APPL";200707"APPL";"4829#6897";"
"A1230 APPL";200701"APPL";"ICMADMIN";"A

Can someone help here?

Re: Converting Text Delimited file into a SAS Dataset

LinusH — Mon, 17 Nov 2008 20:24:53 GMT

First add dlm=';' dsd missover to your infile statement.
dlm specifies the separator between fields.
dsd says that you character data will be in quotes.
missover will stop SAS to bother when reading pass the en of the line.

Doing this, there is still a quote at the end of the DOCNAME column which I can't figure out how to eliminate (except for some dirty compress()/subtr action afterwards). But I'm sure there are some INFILE gurus out there who knows...

/Linus

Re: Converting Text Delimited file into a SAS Dataset

LinusH — Tue, 18 Nov 2008 21:10:00 GMT

I found this hen strolling around in the SAS doc. There is a possibility to use a variable length informat in the INPUT statement. I figure this will work all but one field are fixed length:

DATA EMPS;
INFILE EJS dlm=';' dsd truncover length=linelen;
INPUT
@ 2 FOLDER $4.
@ 48 SERIAL $6.
@ 56 DATE $8. @;

varlen = linelen-66;
input @ 66 DOCNAME $varying20. varlen;

IF FOLDER = 'APPL';
run;

/Linus