topic Re: importing a txt-file correctly in SAS Programming

importing a txt-file correctly

fre — Thu, 09 Jul 2015 09:24:47 GMT

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;

Re: importing a txt-file correctly

RW9 — Thu, 09 Jul 2015 09:40:40 GMT

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.

Re: importing a txt-file correctly

fre — Thu, 09 Jul 2015 09:50:20 GMT

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?

Re: importing a txt-file correctly

RW9 — Thu, 09 Jul 2015 10:20:33 GMT

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.

Re: importing a txt-file correctly

Jim_G — Thu, 09 Jul 2015 10:28:19 GMT

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.

Re: importing a txt-file correctly

fre — Thu, 09 Jul 2015 11:08:35 GMT

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.

Re: importing a txt-file correctly

RW9 — Thu, 09 Jul 2015 12:02:57 GMT

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.

Re: importing a txt-file correctly

fre — Thu, 09 Jul 2015 12:10:36 GMT

thank you all.

Re: importing a txt-file correctly

Ksharp — Thu, 09 Jul 2015 12:16:54 GMT

Use @@ at the end of INPUT .

Re: importing a txt-file correctly

fre — Thu, 09 Jul 2015 13:45:12 GMT

thanks xia, that's indeed what I needed.