Import unsorted data from a notepad file(.txt) into SaS

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

You need to provide an example of the output you want. You probably also need to be clearer about what parts of that file you want read as the data. Do you know the variable names in advance? — Tom
I am trying to import a text file with unsorted data. The column and data against the column are in same line separated by space ' '. There are around 100 columns. The tables are repeating with same column names but with different data till the end of file. The above example of shows the input file and I need the output in correct table form in SAS. — Ather Alam Khan
This TXT file seems to be a software system dump. Are there other format types available such as XML/JSON of same data as this file looks a bit of a tree type structure? — Parfait
Thank you for answering the question, we dont have source, we only get this file on daily basis in this format. The data is not stored anywhere except in this file and we want the data to be available with us to perform different analytics. This is the reason of importing the file in SAS. Thanks — Ather Alam Khan

Tom Tom · Accepted Answer · 2016-07-21T14:11:25

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;

Import unsorted data from a notepad file(.txt) into SaS

1 Answers