How to import a REALLY MESSY text file into SAS?

Reply
New Contributor
Posts: 4

How to import a REALLY MESSY text file into SAS?

I have to import a really messy text file into sas, well maybe it's not that messy for someone here.:smileysilly:

It's an obs below:

|992ED7A8-72B9-4130-87E6-8397BC458DDD|,|Van Scoyoc Associates|,|Van Scoyoc Assoc|,|y|,|Alameda-Contra Costa Transit District|,|Alameda Contra Costa Transit District|,|Alameda Contra Costa Transit District|,20000.0,|T4000|,|WebAK|,|n|,| |,|y|,|y|,|2011|,|q3|,|THIRD QUARTER REPORT|,||

There're 18 variables in this text file, and tens of thousands of obs. I uploaded a part of the data with the file name tast.txt.

All the values are in "| |", and there're some missing values too.

How can I import all the data into a sas dataset?

Thanks a lot!

Attachment
Attachment
Esteemed Advisor
Posts: 7,293

Re: How to import a REALLY MESSY text file into SAS?

Doesn't really look that messy at all. It looks like you can easily input the file in one data step by changing all of the | characters to be double quote (") characters, then the file can be input as a comma delimited file.

e.g., the start of your program might look like (assuming your data is in the c:\temp directory):

data want;

  infile "c:\temp\task.txt" dlm="," dsd lrecl=32286;

  input @;

  _infile_=translate(_infile_,'"','|') ;

New Contributor
Posts: 4

Re: How to import a REALLY MESSY text file into SAS?

I wish I could, but there're tens of thousands observations in that dataset.:smileyconfused:

New Contributor
Posts: 4

Re: How to import a REALLY MESSY text file into SAS?

I wish I could, but there're tens of thousands of observations in that text file.:smileyshocked:

Esteemed Advisor
Posts: 6,656

Re: How to import a REALLY MESSY text file into SAS?

You only have to formulate the input statement for all variables once, and then the number of lines is irrelevant (unless you run out of disk space for the SAS dataset).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,775

Re: How to import a REALLY MESSY text file into SAS?

This is the part of @arthur's program that changes pipe (|) to double quote (")

_infile_=translate(_infile_,'"','|') ;


Then you just follow that with the proper input statement to read the 18 variables, and you're home in time for dinner.

New Contributor
Posts: 4

Re: How to import a REALLY MESSY text file into SAS?

thanks I will try:smileylove:

Ask a Question
Discussion stats
  • 6 replies
  • 307 views
  • 2 likes
  • 4 in conversation