Import unsorted data from a notepad file(.txt&

Question

I have a big text file and it contains 3 tables. The records in the file are unsorted, the records in the file are in line with the column name separated by space. The tables are repeating themselves until the end. I want to import the data from that notepad file under correct table into SAS. I want to read the records and column names from the text file and put them under correct table In SAS. I tried through INFILE and I was successful in importing the data into SAS, but since Columns are unsorted and tables are repeating themselves therefore it is harder to keep the records under correct table in SAS. I am a beginner to SAS, any help would be greatly appreciated.

The below is the example of data in the text file

 ABCD - ABCD          ----     Table 1                                                                         

 Column1           xxxxxxxxxxxxxxxxxxx                     Column3 xxxxxxxxxxxx                                                 
 Column2          x                                        Column4 xx

  ABCD - ABCD          ----     Table 2                                                                       

 Column1           xxxxxxxxxxxxxxxxxxx                     Column3   xxxxxxxxxxx                                                
 Column2          x                                        Column4 xx

  ABCD - ABCD          ----     Table 3                                                                       

 Column1           xxxxxxxxxxxxxxxxxxx                     Column3   xxxxxxxxxxxxxxx                                                
 Column2          x                                        Column4 xx

  ABCD - ABCD          ----     Table 1                                                                         

 Column1           xxxxxxxxxxxxxxxxxxx                     Column3   xxxxxxxxxxxxxx                                                 
 Column2          x                                        Column4 xx

  ABCD - ABCD          ----     Table 2                                                                         

 Column1           xxxxxxxxxxxxxxxxxxx                     Column3   xxxxxxxxxxxxxxxxxx                                                 
 Column2          x                                        Column4 xx

  ABCD - ABCD          ----     Table 3                                                                         

 Column1           xxxxxxxxxxxxxxxxxxx                     Column3   xxxxxxxxxxxxxxxxxxxx                                               
 Column2          x                                        Column4 xx

Answer 1

Normally when reading a report you will want to have logic to detect where in the report you are. You probably will want to read and retain values from header lines. Normally that might include the report date, the individual the report is for or, as in your case, which part of the report the data is from.

data step1 ;
  infile 'myfile.txt' truncover ;
  input @;
  * eliminate blank lines ;
  if _infile_ = ' ' then delete;
  * Read the TABLE name ;
  if substr(_infile_,23,6) = ' ---- ' then do;
    block+1;
    input @33 table_name $32.;
    retain table_name;
    delete;
  end;
  else do;
    * Read two values from each line ;
    input @2 varname $20. value $50. @;
    output;
    input @59 varname $20. value $50. ;
    output;
  end;
run;

You can then add steps to sort and transpose.

 proc sort data=step1 out=step2;
   by table_name block varname ;
 run;
 proc transpose data=step2 out=step3;
   by table_name block;
   id varname;
   var value;
 run;