topic Re: How to get uneven row to single row in SAS Programming

How to get uneven row to single row

Sharath_naik — Fri, 09 Oct 2020 13:55:19 GMT

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

Re: How to get uneven row to single row

ballardw — Fri, 09 Oct 2020 14:13:11 GMT

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.

Re: How to get uneven row to single row

Reeza — Fri, 09 Oct 2020 15:28:27 GMT

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

Re: How to get uneven row to single row

Sharath_naik — Fri, 09 Oct 2020 15:57:33 GMT

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

Re: How to get uneven row to single row

Sharath_naik — Fri, 09 Oct 2020 15:58:41 GMT

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

Re: How to get uneven row to single row

Sharath_naik — Fri, 09 Oct 2020 16:00:51 GMT

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

Re: How to get uneven row to single row

Reeza — Fri, 09 Oct 2020 16:34:16 GMT

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

Re: How to get uneven row to single row

RichardDeVen — Tue, 13 Oct 2020 11:08:03 GMT

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)

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;

Re: How to get uneven row to single row

Sharath_naik — Mon, 12 Oct 2020 17:01:48 GMT

Thanks Bro, It worked