Read the data from specific column using delimiters

[UshaMH]

Hi Team,

I would need to read the print the group details (which I highlighted) form the below sample. I have tried with substr and scan function but unable to get desired output. Could anyone able to help me on this please?

Command Audit Trail for USER IBMUSER

Segment: CICS                      Added on 05.241/03:19 by C4RTEST
                                               Changed on 05.241/03:20 by C4RTEST
                 TSO                       Changed on 05.241/03:19 by C4RTEST
Attrib:       PASSWRD             Removed on 05.238/14:24 by C4RTEST
                 INTERV                 Changed on 05.241/04:42 by C4RTEST
                 RESTR                  Added on 05.238/14:24 by C4RTEST
Connect:                                 BCSC Added on 05.238/14:24 by IBMUSER
                                                GROUP2 Added on 05.238/14:24 by IBMUSER
                                                GROUP3A Removed on 05.238/14:24 by IBMUSER
GrpAttr:    ADSP                      BCSC Removed on 05.238/14:24 by IBMUSER

[PeterClemmensen]

Is this a TXT file or? Please be more specific 🙂

[UshaMH]

Hi Peter,

Thanks for coming back.  I am reading from mainframe dataset.

[ballardw]

What should happen with all the other rows?

Is the order of the headings: Segment, Attrib, Connect, GrpAttr always the same and in that order??

Will the values after connect always consist of exactly 3 rows?

Do you want only the words highlighted in yellow (kind of waste) or the entire line? What if one of the lines in that connect block to do not start with BCSC, Group2 or Group3a?

Does the file contain more than one Connect block?

For the example shown I think the data set generated is what you intend but don't quite state as wanted:

data junk;
  /* for your use you would use the following
  infile "path and file name of source file";*/
  /* is use this to demostrate with inline data*/
   infile datalines ;
   length string $ 200.;
   input ;
   if _infile_ =: 'Connect' then do;
      /* this is where the number of lines being the same is important*/
      string= left(substr(_infile_,9));
      output;
      input;
      string= left(_infile_);
      output;
       input ;
      string= left(_infile_);
      output;
   end;
datalines;
Segment: CICS                      Added on 05.241/03:19 by C4RTEST
                                               Changed on 05.241/03:20 by C4RTEST
                 TSO                       Changed on 05.241/03:19 by C4RTEST
Attrib:       PASSWRD             Removed on 05.238/14:24 by C4RTEST
                 INTERV                 Changed on 05.241/04:42 by C4RTEST
                 RESTR                  Added on 05.238/14:24 by C4RTEST
Connect:                                 BCSC Added on 05.238/14:24 by IBMUSER
                                                GROUP2 Added on 05.238/14:24 by IBMUSER
                                                GROUP3A Removed on 05.238/14:24 by IBMUSER
GrpAttr:    ADSP                      BCSC Removed on 05.238/14:24 by IBMUSER
;

What this does:

Input; reads the current line of the data file into the buffer

_infile_ is a SAS temporary variable that holds the line of data (limit of 32K characters).

Then you can test whether the string _infile_ has desired text such as "Connect" to identify specific line by value.

Once you find that line then you can extract the remainder of the line, then read additional lines.

If the number of lines to be read ever varies the shown code is not correct. Additional code would need to be used to only read lines until the next "block" of values is included. But you need to know what that next block starts with, or might start with.

Then if there are supposed to be multiple "Connect" blocks to read you need to continue reading.  The above code actually does that, but assumes if another connect is encountered it will have exactly 3 lines to use.

[UshaMH]

I think I couldn't post my query in a clear way.Sorry for that.
The sample input which I have is the audit trial for the user

When there is any change in user fields like Segment,Attributes,Connect or GrpAttr , it will get listed in the audit trial.So the order of heading may differ for every user.

User may request for more than one group connection (or) removals.So, the number of rows after the 'Connect' keyword may vary.

BCSC,Group2 and Group3a - These are the groups added/removed from the user on the respective dates(05.238/14:24) by IBMUSER.
I am just intersted in the groups which are connected/removed from the user. We don't get the same group (ie BCSC,Group2,Group3a) every time.

The file contains only one connect block.And I just need the group details inside the connect block.I don't want to print any other lines.

[ballardw · Accepted solution]

---

@UshaMH wrote:

I think I couldn't post my query in a clear way.Sorry for that.
The sample input which I have is the audit trial for the user

When there is any change in user fields like Segment,Attributes,Connect or GrpAttr , it will get listed in the audit trial.So the order of heading may differ for every user.

User may request for more than one group connection (or) removals.So, the number of rows after the 'Connect' keyword may vary.

BCSC,Group2 and Group3a - These are the groups added/removed from the user on the respective dates(05.238/14:24) by IBMUSER.
I am just intersted in the groups which are connected/removed from the user. We don't get the same group (ie BCSC,Group2,Group3a) every time.

The file contains only one connect block.And I just need the group details inside the connect block.I don't want to print any other lines.

---

This is a modification of the previous to read an irregular number of lines after the connect.

data junk;
  /* for your use you would use the following
  infile "path and file name of source file";*/
  /* is use this to demostrate with inline data*/
   infile datalines ;
   length string $ 200.;
   input ;
   if _infile_ =: 'Connect' then do;
      /* first read the remainder of the first line
         and write to outpu
      */
      string= left(substr(_infile_,9));
      output;
      input;
      Do until (0<index(_infile_,':')<15);
         string= left(_infile_);
         output;
         input ;
      end;
   end;
datalines;
Segment: CICS                      Added on 05.241/03:19 by C4RTEST
                                               Changed on 05.241/03:20 by C4RTEST
                 TSO                       Changed on 05.241/03:19 by C4RTEST
Attrib:       PASSWRD             Removed on 05.238/14:24 by C4RTEST
                 INTERV                 Changed on 05.241/04:42 by C4RTEST
                 RESTR                  Added on 05.238/14:24 by C4RTEST
Connect:                                 BCSC Added on 05.238/14:24 by IBMUSER
                                                GROUP2 Added on 05.238/14:24 by IBMUSER
                                                GROUP3A Removed on 05.238/14:24 by IBMUSER
                                        Somehthing added
GrpAttr:    ADSP                      BCSC Removed on 05.238/14:24 by IBMUSER
;

The Do Until is looking for : character in the first 14 characters of the line as a signal that the line would be a new topic like GrpAttr: Perhaps you could use a smaller number but with complete examples I guessed on 14. It apparently can not be too large of avalue as you have lots of : in the time portion and I tried to avoid hitting where they seem to typcially occur in the body of the connect group.

Sequence of operations is important in this sort of file parsing so modify the code very carefully.

Note: If you have multiple Connect sections this might find all of them.