Read Infile Right to Left?

Reply
Super Contributor
Posts: 418

Read Infile Right to Left?

Hello! I really hope this works.. Okay, is it possible for sas to have a defined infile statement read variables from Right to Left?

The issue I have is one of my colleagues saved a datatape from a sql table as a Pipe delimited txt file. Unknown to him, one of the comment fields within the datatable had Pipes in it. Apparently sql server doesn't put quotes around character fields that have delimiters within them, so I can't read this file into sas without a bunch of the columns being shifted.

However, I did notice that the comment field was the second field within the 50+ field table, so I was curious if it would be possible to read an infile statement from right to left, and then simply drop the first two columns regardless of what data they say?

I have never heard of this and couldn't find anything online, but if possible it would greatly help me solve a problem, so hopefully someone can help!

Thanks again.

For An Example of the tab data that I'm having problems with, please  see the below. I added quotes to the comment field so it would be easier to read, the actual file has no quotes here (but needs them!).

ID|Answer|Comment|Processvar|var1|var2|var3|va4

1|No|"User answer | Process 1 | new california wins"|new|7|3|8|3

2|YES|"User answer | Process 1"|new|7|3|8|3

3|YES|"User answer | Process 1"|new|7|3|8|3

4|NO|"User answer | Process 1"|new|7|3|8|3

This file should only be 8 distinct fields, however it is coming out as 10,9,9,9, due to the pipes in the comments.

Thanks for your help!

Brandon

PROC Star
Posts: 7,363

Re: Read Infile Right to Left?

Based on your sample, what should the output file look like?

Super Contributor
Posts: 418

Re: Read Infile Right to Left?

I have attached an excel file for the output. Please note that the comment field in the actual file DOES NOT have quotes in it.

Also, I was thinking I could read it in right to left in order to get the value from number 1 for comparsion.

Basically I have pipe delimiters in a pipe delimited file with NO way to know what is a delimiter and what goes inside a comment (Because microsoft sql server doesn't put quotes around it character fields!).

So my "solution" was to read the txt file in, do a reverse on the infile statment, and then output it to a new file, which I would then read in.. I don't know how to do that tho, My atttempt below did not work.

data _null_;

infile "MYFILE.txt"  lrecl=1000000 end=eof;

file "NEWFILE.txt";

newvar=reverse(_infile_);

put newvar;

run;

PROC Star
Posts: 7,363

Re: Read Infile Right to Left?

Does the following do what you want?:

data want;

  infile cards delimiter='|' firstobs=2;

  input;

  id=scan(_infile_,1,'|');

  answer=scan(_infile_,2,'|');

  comment=scan(_infile_,3,'|');

  processvar=scan(_infile_,-5,'|');

  var1=scan(_infile_,-4,'|');

  var2=scan(_infile_,-3,'|');

  var3=scan(_infile_,-2,'|');

  var4=scan(_infile_,-1,'|');

  CALL SCAN(_infile_, 3, start, length , '|');

  CALL SCAN(_infile_, -5, end, length , '|');

  comment=substr(_infile_,start,end-start-1);

  cards;

ID|Answer|Comment|Processvar|var1|var2|var3|va4

1|No|User answer | Process 1 | new california wins|new|7|3|8|3

2|YES|User answer | Process 1|new|7|3|8|3

3|YES|User answer | Process 1|new|7|3|8|3

4|NO|User answer | Process 1|new|7|3|8|3

;

Message was edited by: Arthur Tabachneck.  Corrected to include the right pointers as pointed out by Tom Abernathy

Super User
Super User
Posts: 6,502

Re: Read Infile Right to Left?

Art -

You need to change

  CALL SCAN(_infile_, -4, end, length , '|');

  comment=substr(_infile_,start,end-1);

to :

  CALL SCAN(_infile_, -5, end, length , '|');

  comment=substr(_infile_,start,end-start-1);

PROC Star
Posts: 7,363

Re: Read Infile Right to Left?

: Obviously, I hadn't tested the code and thank you for correcting it!  I've corrected the code in my original response.

Ask a Question
Discussion stats
  • 5 replies
  • 399 views
  • 0 likes
  • 3 in conversation