importing a txt-file correctly

[fre]

Hi,

I'm having a problem with importing a TXT file correctly.

Normally, the file has a lot of lines with each 84 characters. = 1 record

With the code I have here-under, I can perfectly import the text-file as I want. (one line = 1 record)

Now, I received an update of this TXT-file, and on each line, there are 8400 characters.    (one line = 100 records)

The second line start with record 101.

When I use this code on this updated file, SAS only reads the first 84 characters on the first line and does not read characters 85-8400 on the first line, which are records 2-100.

It directly reads line 2 which contains record 101.

My question:  how can I tell SAS to read character 85-169 as the second record, character 170-254 as the third record, etc  ?

Anyone has an idea?

-----------------------

%macro readOneFile(path_in=,file_in=,file_out=);

DATA &file_out.;
INFILE "&path_in.&file_in."
DSD DELIMITER=""  TRUNCOVER
;

LENGTH
  var1 $ 5
  var2 $ 5
  var3 $ 3
  var4 $ 10
  var5 $ 10
  var6 8
  var7 8
  var8 8
  var9 8
  var10 8
;

INPUT
  @1 var1 $5.
  @6 var2 $5.
  @11 var3 $3.
  @14 var4 $10.
  @24 var5 $10.
  @34 var6 7.
  @41 var7 11.
  @52 var8 10.
  @62 var9 12.
  @74 var10 11.
;

RUN;

%mend fileread;

[RW9]

Try the lrecl option on the infile:

infile "&path_in.&file_in." dsd truncover lrecl=32767;

To be honest though, you should setup a data transfer document which details the structure of the file, otherwise there is nothing to stop them updating the file each time.  I would also suggest to use CSV, or XML structure for the file.  Made up file formats like this just make the data harder to access.

[fre]

Hi,

thanks for your answer.

I tried the lrecl=32767 option, but the result is the same:

- the first line in my table contains line 1 character 1-84 (=record 1)

- the second line in my table contains line 2 character 1-84 (= record 101)

records 2 - 100 are not read by SAS. (which are on line 1 in the TXT)

Is there a way to tell SAS that each 85th character is the start of a new record?

[RW9]

Sorry, its quite confusing.  Maybe post the file, or a sample thereof.  It does sound very much like you need to go back and get the file being sent converted into something resembling a more useful structure.

[Jim_G]

Write a SAS program that reads the new file  LRECL=8400.  Define 100 variables, each 84 characters long.  Output  each variable 84 characters long (do loop 100 times) to a TXT file. Then read that TXT file with your current program.

[fre]

Thanks for your answers.

@RW9:

imagine you have a txt-file which contains on 1 line 840 characters, which represent 10 records/observations. 

SAS should read the first 84 characters and put them in a table as the first record. 

Next it should read the next 84 characters (position 85 - 169) of the first line and put them in the table as the second record

Next it should read the next 84 characters (position 170 - 254) of the first line and put them in the table as the third record

and so on.

Is this possible?  Or do I have to ask my source to alter the source file with only 84 characters per line?

@JIM

Just saw in the TXT-file that the end of line 1 and the beginning of line 2 contains each a part of the same record.

Guess it will become difficult to use that file.

[RW9]

Well, its possible.  As Jim_G has mentioned.  In your import program, read the whole of line 1 into one variable.  Then do a loop over the line to output 10 sections:

data want;

     infile xyz... lrecl=8400;

     input the_full_line $;

     do i=1 to 10;

          out_var="";

          do j=1 to 84;

               out_var=strip(each_bit)||substr(the_full_line,(i-1 * 84)+j,1);

          end;

          output;

     end;

run;

Or something similar to the above.  This would read the whole text from line 1, then break it into 10 sections of 84 characters, and output.

I would still advise however to go with a better structure of datafile, setup a data import agreement, agree on a proper structure, would make your life easier in the long run.

[fre]

thank you all.

[Ksharp · Accepted solution]

Use @@ at the end of INPUT .

[fre]

thanks xia, that's indeed what I needed.