DATA Step, Macro, Functions and more

Converting Text Delimited file into a SAS Dataset

Reply
N/A
Posts: 1

Converting Text Delimited file into a SAS Dataset

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?
Super User
Posts: 5,431

Re: Converting Text Delimited file into a SAS Dataset

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
Data never sleeps
Super User
Posts: 5,431

Re: Converting Text Delimited file into a SAS Dataset

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
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 120 views
  • 0 likes
  • 2 in conversation