How to import messy text file into SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

How to import messy text file into SAS

I'm trying to import data from a text file into SAS, but as seen on the screenshot, some observations of the text file don't have information in the first variable. So when imported to SAS, the dataset seem like a mess. The following observation's information will appear in the variable where the previous observation's only value used to be. 

How can I import all the data into SAS from this kind of text file?

Thanks a lot!


2.jpg1.jpg

Accepted Solutions
Solution
‎02-03-2015 12:52 PM
Super User
Super User
Posts: 6,845

Re: How to import messy text file into SAS

The picture you posted of the text file makes it look very orderly.  Why are you having trouble reading it?

data want ;

  infile 'myfile.txt' lrecl=32000 truncover firstobs=4 ;

  informat date_announced date_effective mmddyy10.;

  format date_announced date_effective mmddyy10.;

  input buyer_name $1-30 trader_name $31-60 ..... ;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,820

Re: How to import messy text file into SAS

You should read your text file with  Column Input, such as

length aName TName $30;

input AName 1-30 TName 31-60 ... ;

PG

PG
Super User
Posts: 11,134

Re: How to import messy text file into SAS

To add briefly to PGStats response

If you KNOW that only the first two variables are your problem variables you can read the remainder of the data with list input. If you have multiple problems you  may need to read the  entire set with column input.

You may also need to look at MISSOVER, STOPOVER or TRUNCOVER options on your infile statement as it looks like you have some first variable values appearing as ticker symbols. Do you have any messages in the log about "continued on next line" or similar?

Occasional Contributor
Posts: 6

Re: How to import messy text file into SAS

3.jpg

yes, I have the note in the log."SAS went to a new line when INPUT statement reached past the end of a line."

Super User
Posts: 11,134

Re: How to import messy text file into SAS

Easiest likely fix is MISSOVER.

You don't mention the actual file format but if you have incomplete records, such as only  the first 5 of 10 fields are populated when the input statement tries to read the 6th field then by default SAS goes to the next record in the file and continues. MISSOVER tells SAS to treat all of the unread fields as missing and to start over at the beginning of the next record in the first column.

Occasional Contributor
Posts: 6

Re: How to import messy text file into SAS

thank you very much!

I'm gonna try later!

Super User
Super User
Posts: 6,845

Re: How to import messy text file into SAS

MISSOVER should actually almost never be used since it can cause some data to not be read. Use TRUNCOVER instead.

Super User
Super User
Posts: 6,845

Re: How to import messy text file into SAS

Hard to offer a firm recommendation based on a photograph of the data.

Looks to me like the data is well formed.

What problem are you having with it?

Super User
Super User
Posts: 6,845

Re: How to import messy text file into SAS

Looks like perhaps what has happened is they did not replicate the data in the first four fields when it was the same as for the previous records?  Like the results you get with PROC REPORT using an ORDER or GROUP variable.

You should be able to fix that very easily.  For example you could just add a data step and use some LAG() and COALESEC() function calls.

data have ;

  length Buyer_Name Target_Name $50 Buyer_ID Target_ID $10 Buyer_Symbol $10 ;

  input Buyer_Name -- Buyer_Symbol ;

  infile cards dsd ;

cards;

A,B,1,2,XX

,,,,YY

C,D,3,4,ZZ

,,,,ZYY

;;;;

data want ;

  set have;

  Buyer_Name = coalescec(Buyer_Name,lag(Buyer_Name));

  Target_Name = coalescec(Target_Name,lag(Target_Name));

  Buyer_ID = coalescec(Buyer_ID,lag(Buyer_ID));

  Target_ID = coalescec(Target_ID,lag(Target_ID));

run;

Occasional Contributor
Posts: 6

Re: How to import messy text file into SAS

thanks for the code tom!

but when I try to import the data from text file to sas, it seems like the result is exactly the same like what I got yesterday by using column input.

is there any solution that I can delete the observations with missing values in all variables but the 6th variable?

Solution
‎02-03-2015 12:52 PM
Super User
Super User
Posts: 6,845

Re: How to import messy text file into SAS

The picture you posted of the text file makes it look very orderly.  Why are you having trouble reading it?

data want ;

  infile 'myfile.txt' lrecl=32000 truncover firstobs=4 ;

  informat date_announced date_effective mmddyy10.;

  format date_announced date_effective mmddyy10.;

  input buyer_name $1-30 trader_name $31-60 ..... ;

run;

Occasional Contributor
Posts: 6

Re: How to import messy text file into SAS

thanks a lot!

didn't know "lrecl=32000 truncover" before .

but it works. now I get all the data into sas.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 709 views
  • 8 likes
  • 4 in conversation