How to get uneven row to single row

[Sharath_naik]

SL_no	Column	End result
1	A078      B D BLEDSOE
2	DRAWINGS NOT CHANGED	A078       B D BLEDSOE    DRAWINGS NOT CHANGED
3	A050    D A MATHISON
4	OWNERSHIP OF
5	APPLICABLE	A050    D A MATHISON   OWNERSHIP OF APPLICABLE
6	A070       B D BLEDSOE
7	DRAWINGS NOT	A070       B D BLEDSOE DRAWINGS NOT

1 and 2 row is one group

3,4,5, are another group

6 and 7 are another group 

Please help me 

[ballardw · Accepted solution]

Do you have exactly 7 rows of data in your data set? If not, you will need to provide rules based on the content of your variable that tells which ones need to be combined.

[Sharath_naik]

It's more than 7 lines... could you please share the combined logic.... I'm worried because some group has 2 lines, some group as 3 lines

[Reeza]

You've marked the question as solved so I'm assuming no further responses are required.

[Reeza]

Did you originally read this from a text file?

Do you know if the pattern of A### is an identifier for the start of each record? If so, this is pretty easy but if the pattern changes it's not as easy.

---

@Sharath_naik wrote:

SL_no	Column	End result
1	A078      B D BLEDSOE
2	DRAWINGS NOT CHANGED	A078       B D BLEDSOE    DRAWINGS NOT CHANGED
3	A050    D A MATHISON
4	OWNERSHIP OF
5	APPLICABLE	A050    D A MATHISON   OWNERSHIP OF APPLICABLE
6	A070       B D BLEDSOE
7	DRAWINGS NOT	A070       B D BLEDSOE DRAWINGS NOT

1 and 2 row is one group

3,4,5, are another group

6 and 7 are another group 

Please help me 

---

[Sharath_naik]

Thanks for Replying @Reeza . 🙂

#A### is a partern ...#A or #B or #C will be generated going forward.... 

It's not .txt, IT;s a mainframe data,... But in Sorted order 

[Sharath_naik]

Thanks for replying Reeza,,,,, Yes its a pattern.. could you please share the solution

[RichardDeVen]

You can use PRXMATCH to detect the 'new group of records' start condition.

Use a retained result string to accumulate a concatenation of each row in the group.

Example (generate some sample data)

Spoiler

data have;
  call streaminit(123);
  do rownum = 1 to 1000;
    length mfrecord $25;  /* mainframe record */
    if rand('uniform') < 0.25 or rownum in (1, 101,102,103) then do;
      mfrecord = 
        byte(64+rand('integer',26)) ||
        put(rand('integer',999),z3.) ||
        '   AAAAAAAAA'
      ;
      seq = 1;
    end;
    else do;
      seq + 1;
      mfrecord = repeat(byte(64+seq), rand('integer',0,24));
    end;
    OUTPUT;   
  end;

  keep rownum mfrecord;
run;

Example (compute result as a group aggregate that is concatenation of rows)

Version 1. One result row for each group

data want;
  set have end=done;
  length result $1000;  retain result;

  if prxmatch('/^[A-Z]\d+ /', mfrecord) then do;
    if _n_ > 1 then OUTPUT;
    call missing(result);
  end;

  result = catx(' ', result, mfrecord);

  if done then OUTPUT;

  keep result;
run;

Version 2. Concatenation result on last record in group

Requires lead processing due to synthetic group definition

data want;
  /* MERGE with NO BY variables is 1:1 merge and provides a LEAD variable 'nextrecord' */
  merge
    have
    have(firstobs=2 rename=mfrecord=nextrecord)
    end=done
  ;

  donex = done;

  length accum $1000; retain accum;
  length result $1000;

  if prxmatch('/^[A-Z]\d+ /', nextrecord) then do;
    result = catx(' ', accum, mfrecord);
    OUTPUT;
    group_id + 1;
    call missing(accum, result);
  end;
  else do;
    accum = catx(' ', accum, mfrecord);
    if done then result = accum;
    OUTPUT;
  end;

  keep rownum mfrecord result group_id donex nextrecord;
run;

Aggregating can be simplified if you code a grouping view first

data groups;
  set have;
  if prxmatch('/^[A-Z]\d+ /', mfrecord) then groupno+1;
run;

data want;
  do until (last.groupno);
    set groups;
    by groupno;

    length accum result $1000;

    accum = catx(' ', accum, mfrecord);

    if last.groupno then result = accum;

    output;
  end;
  drop accum;
run;

[Sharath_naik]

Thanks Bro, It worked