SAS find first value in column and get row number

0
votes

I'm trying to import and clean some file where the first rows of column 1 contains description and column names. the data begins after the row containing "BEGINDATA". Is it possible to search sas table for "BEGINDATA" and assign the row number to a macro variable? In the next data step I could use firstobs=macro variable to load only data.

Thanks for any hint!

2
sasposition
What format is the file in? – Tom
It is a datfile which I already imported to SAS. – mile.d
Not sure what a "datfile" is but if your source file is simple text file, like a CSV file, then the data step that reads it can also just ignore the lines before "BEGINDATA". It is best to avoid using tools like PROC IMPORT to guess what is in your files. – Tom

2 Answers

1
votes

Certainly! See this example.

/* Generate example data */
data have;
    do i = 1 to 10000;
        if(i = 100) then description = 'BEGINDATA';
            else call missing(description);

        value = rand('uniform');
        output;
    end;

    drop i;
run;

/* Get row where the data begins. Only keep the description variable 
   to reduce the size of the PDV */
data _null_;
    set have(keep=description);

    if(description = 'BEGINDATA') then do;
        call symputx('startrow', _N_, 'G');
        stop;
    end;
run;

/* Read from the data start row */
data want;
    set have(firstobs=&startrow.);
run;
0
votes

Not much you can do after it is already "imported". But if your source file is just a text file then your data step that reads it can just skip the prefix in the same step that reads the data.

data want;
  infile 'myfile.csv' dsd truncover ;
  input test :$20. @1 @;
  do while (test ne 'BEGINDATA');
     input ;
     input test :$20. @1 @;
  end;
  * Code to read the actual data lines ;
run;