Invalid Data Error

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

Invalid Data Error

Hello SAS Community,

 

I am trying to read a number of .txt files into SAS.

I am enclosing a sample of my data and a screenshot how the data end up in SAS.

This is the code that I have used:

 

libname lib 'E:\LSE_Tick1';

%let dirname = E:\LSE_Tick1;
filename DIRLIST pipe "dir /B &dirname\*.txt";
data dirlist ;
     length fname $256;
     infile dirlist length=reclen ;
     input fname $varying256. reclen ;
run;

data lib.LSE_tick (drop=fname);
  length myfilename $100;
  set dirlist;
  filepath = "&dirname\"||fname;
  infile dummy filevar = filepath length=reclen end=done DSD dlm= ' ' TRUNCOVER  firstobs=3; 
do while(not done); 
myfilename = filepath;
length TICode $12 TIDM $3 IssuerName $30 Cy $2 Cur $3 Segm $4 Sect $4 TT $2 I $1 TradeCode $10 SEDOL $7 P $1;
informat PublTstmp CancTstmp TradeTstmp datetime. ;
do until (done);
input TICode TIDM IssuerName Cy Cur Segm Sect PublTstmp CancTstmp TradeTstmp TradeSize TradePrice TT I TradeCode SEDOL P;
output;
end;
end;
drop myfilename;
run;

After running the code, a list of error messages saying Invalid data for ...  in line ... on the log window.

 

I look forward to your suggestions and thanks in advance!

 

Best.

Sas Data.png

 


Accepted Solutions
Solution
‎12-19-2017 03:54 PM
Super User
Super User
Posts: 7,944

Re: Invalid Data Error

[ Edited ]

Hard to tell without downloading the attachment, but does your data have tabs between the fields?

If so then make sure to tell the INFILE statement that.  It is best to use the hexcode for tab to prevent the editor from converting a quoted tab into a space.

  infile dummy filevar = filepath length=reclen end=done DSD dlm= '09'x TRUNCOVER  firstobs=3; 

If they are not tabs you will have trouble since there a then both many spaces between fields (make the DSD a problem) and embedded spaces in the values of some variables (making list mode in general a problem).  But it looks like your records are fixed width so just read it using the column locations.

 

Otherwise show one or more example error messages from the LOG so we can see which variables for which variables it is not reading properly.

View solution in original post


All Replies
Solution
‎12-19-2017 03:54 PM
Super User
Super User
Posts: 7,944

Re: Invalid Data Error

[ Edited ]

Hard to tell without downloading the attachment, but does your data have tabs between the fields?

If so then make sure to tell the INFILE statement that.  It is best to use the hexcode for tab to prevent the editor from converting a quoted tab into a space.

  infile dummy filevar = filepath length=reclen end=done DSD dlm= '09'x TRUNCOVER  firstobs=3; 

If they are not tabs you will have trouble since there a then both many spaces between fields (make the DSD a problem) and embedded spaces in the values of some variables (making list mode in general a problem).  But it looks like your records are fixed width so just read it using the column locations.

 

Otherwise show one or more example error messages from the LOG so we can see which variables for which variables it is not reading properly.

Contributor
Posts: 59

Re: Invalid Data Error

Hello Tom,

 

Many thanks for your suggestion. It worked!!

Just to understand, I assume 

dlm= '09'x

stands for a tab?  

 

Also, would you have any suggestions for the format of date and time variable (e.g. 29/09/2017 07:15:05)? I tried creating the format but it did not work. 

 

 

 

 

Super User
Super User
Posts: 7,944

Re: Invalid Data Error

[ Edited ]

BchBnz wrote:

Hello Tom,

 

Many thanks for your suggestion. It worked!!

Just to understand, I assume 

dlm= '09'x

stands for a tab?  

 

Also, would you have any suggestions for the format of date and time variable (e.g. 29/09/2017 07:15:05)? I tried creating the format but it did not work. 

 


Yes. 9 is the hexcode for a tab.  '0A'x is a linefeed, '0D'x is a carriage return.  '20'x is a space, '30'x is the digit 0.

Try using the ANYDTDTM informat for those dates.  If it doesn't work then read them as strings and split it into date and time strings and use INPUT() function to read each. You could then use DHMS() function to build a date time value from the results.

Contributor
Posts: 59

Re: Invalid Data Error

Thank you ever so much for the explanation and suggestions, Tom!

Much appreciated.

 

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 319 views
  • 0 likes
  • 2 in conversation