BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BchBnz
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Tom
Super User Tom
Super User

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.

BchBnz
Obsidian | Level 7

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. 

 

 

 

 

Tom
Super User Tom
Super User

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

BchBnz
Obsidian | Level 7

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

Much appreciated.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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