topic Re: Search string in text file to find row to import from in SAS Programming

Search string in text file to find row to import from

TobbeNord — Thu, 13 Feb 2020 12:01:20 GMT

I have several hundreds of tab delimited text files and the data I want to import starts in different rows for each text file.

The data starts at the row after the word "Sleep Stage" .

Can I somehow search for this string in my infile statement? In this example code the data starts at row 205 , but it can vary.

%macro import_file(path, file_name, dataset_name);

data &dataset_name.;
infile "&path.\&file_name." DSD DLM="09"x firstobs=205;
input Sleep $ Position :$10. Time :$8. event :$20. Duration :8.;
run;

%mend;

Re: Search string in text file to find row to import from

andreas_lds — Thu, 13 Feb 2020 12:16:10 GMT

Searching with the infile-statement is not possible. I would use two input statement (untested code):

%macro import_file(path, file_name, dataset_name);
  data &dataset_name.;
    length _dataFound 8;
    retain _dataFound 0;
	drop _dataFound;
	
    infile "&path.\&file_name." DSD DLM="09"x;

    if not _dataFound then do;
      input @;
      _dataFound = find(_infile_, 'Sleep Stage') > 0;
    end;

	if _dataFound then do;
		input Sleep $ Position :$10. Time :$8. event :$20. Duration :8.;
		output;
	end;
  run;
%mend;

EDIT: remove firstobs from infile

Re: Search string in text file to find row to import from

TobbeNord — Thu, 13 Feb 2020 12:24:45 GMT

Thank you!
It almost worked as wanted.
The only little "bug" is that it also import the row with "sleep stage" on it.
It should start import the row below "sleep stage" .

Re: Search string in text file to find row to import from

novinosrin — Thu, 13 Feb 2020 13:07:36 GMT

Hi @TobbeNord To address

'It should start import the row below "sleep stage" .'

Add an ELSE as shown below

 if not _dataFound then do;
      input @;
      _dataFound = find(_infile_, 'Sleep Stage') > 0;
    end;
    else
	if _dataFound then do;
		input Sleep $ Position :$10. Time :$8. event :$20. Duration :8.;
		output;
	end;

Re: Search string in text file to find row to import from

Tom — Thu, 13 Feb 2020 14:22:47 GMT

The data starts at the row after the word "Sleep Stage" .

So just read lines until you find it.

data &dataset_name.;
  infile "&path.\&file_name." DSD DLM="09"x ;
  if _n_=1 then do until (upcase(_infile_) contains 'SLEEP STAGE');
    input;
  end;
  input Sleep $ Position :$10. Time :$8. event :$20. Duration :8.;
run;