I am in need of some guidance. I have searched this forum and the web for a method to accomplish my desired end to no avail. I know how to do it using SAS, but SAS is not an option in this case. I have a pipe delimited text file with multiple records, or lines, per unique primary key value. The first two data elements of each line are the primary key value and a record-type value, respectively. The record-type value specifies which data elements are contained on that line. Data elements with no value are denoted in the file by consecutive delimiters. For each unique primary key value, certain record-types will only exist once, and others may exist multiple times. My problem concerns one of each.
Record Type 01: Only 1 instance per primary key value. Input: 35 data elements. Desired Output: 34 data elements - Drop 2nd data element.
Record Type 16: Up to 5 instances per primary key value. Input: 5 data elements. Desired Output: 3 data elements - Drop 1st & 2nd data elements.
I am trying to find out how to read-in the file, and for each primary key value, only output the type 01 and 16 records as a new single record appended to the end of an existing range. From the output, I want to drop the 2nd data element from the record-type 01 records, and the 1st and 2nd data elements from the record-type 16 records. Also, because there are 5 options for the record-type 16 records, my output destination range will contain 3 columns for each of them. If it's possible, I would like to align the record-type 16 data in the output range by the option type.
I have attached samples of both the text file and desired output.
Any and all assistance is greatly appreciated.