topic Invalid Data Error in SAS Programming

Invalid Data Error

BchBnz — Tue, 19 Dec 2017 19:18:51 GMT

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.

Re: Invalid Data Error

Tom — Tue, 19 Dec 2017 19:54:01 GMT

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.

Re: Invalid Data Error

BchBnz — Tue, 19 Dec 2017 20:37:42 GMT

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.

Re: Invalid Data Error

Tom — Tue, 19 Dec 2017 20:59:55 GMT

@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.

Re: Invalid Data Error

BchBnz — Tue, 19 Dec 2017 21:15:36 GMT

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

Much appreciated.