topic Re: Merge cell vertically in proc report, with missing cells, keep the order of file in ODS and Base Reporting

Merge cell vertically in proc report, with missing cells, keep the order of file

SSH2 — Tue, 27 Feb 2018 20:26:58 GMT

The original table was sorted by "Num".

I would like to

1. merge the cells in column "group" if they have the same value and if they are adjacent ("Num" value are contiguous).

2. The new table will keep the same order .

3. The missing cells will not merge.

The Original SAS dataset is:

Variable	Group	Num
drugL		1
drugZ		2
drugC	D	3
drugD	B	4
drugE	B	5
drugF	B	6
drugX	B	7
drugH	B	8
drugI	C	9
drugJ	B	10
drugK	B	11
drugL	F	12

The ideal output table is attached. Thank you for your help!

Re: Merge cell vertically in proc report, with missing cells, keep the order of file

ArtC — Tue, 27 Feb 2018 06:57:34 GMT

I am not sure exactly what you mean by 'merged cells'. That term has a specific meaning that to me is not implied by your question., So let me take a guess and see if it gets us closer. Do you mean that you do not want the GROUP variable to not repeat? If so then we can control order and the display of the GROUP variable through the use of a format. The following creates a format that can then be used as a grouping variable without the complicating NUM variable on the COLUMN statement.

data have;
input variable $ @10 group $1. @13 num;
datalines;
name        1
drugA       2
drugB    D  3
d_drug01 B  4
d_drug02 B  5
d_drug03 B  6
d_drug04 B  7
d_drug05 B  8
d_drug06 B  9
d_drug07 B  10
DrugF    C  11
DrugX    F  12
run;
data control(keep=fmtname start label);
   retain fmtname 'numord';
   set have(rename=(num=start group=label));
   run;

proc format cntlin=control;
   run;
proc report data=have;
   column num variable;
   define num / group f=numord.;
   define variable / display;
   run;

Re: Merge cell vertically in proc report, with missing cells, keep the order of file

SSH2 — Tue, 27 Feb 2018 20:29:09 GMT

Thank you very much ArtC.

I am sorry I confused you. There is new condition just added. I edited the original question. Thank you for your help!

Re: Merge cell vertically in proc report, with missing cells, keep the order of file

ArtC — Tue, 27 Feb 2018 18:27:34 GMT

The SPANROWS option will get you closer, however the missing cells will also merge.

proc report data=have spanrows;
   column num variable;
   define num / group f=numord. style(column)={vjust=c just=c};
   define variable / display;
   run;

Re: Merge cell vertically in proc report, with missing cells, keep the order of file

SSH2 — Tue, 27 Feb 2018 20:32:46 GMT

Thank you. I tried the spanrows. But it is not ideal when there is missing cells. I don't want to merge the missing cells. Thank you very much for your time.

Re: Merge cell vertically in proc report, with missing cells, keep the order of file

ArtC — Thu, 01 Mar 2018 18:32:09 GMT

One way would be to change the value of the missings to different non printable characters. This version of the CONTROL data set will insert nonprintable values into cells with missing values. because they are different they will not group but will still be blank. You would need to adjust the code if there are more than 30 or so missing values.

data have;
input variable $ @10 group $1. @13 num;
datalines;
name        1
drugA       2
drugB    D  3
d_drug01 B  4
d_drug02 B  5
d_drug03 B  6
d_drug04 B  7
d_drug05 B  8
d_drug06 B  9
d_drug07 B  10
DrugF    C  11
DrugX    F  12
Drugy       13
Drugz       14
Drugq       15
Drugr       16
run;
data control(keep=fmtname start label);
   retain fmtname 'numord';
   set have(rename=(num=start));
   if group=' ' then do;
      cnt+1;
      label=byte(4+put(cnt,3.));
   end;
   else label=group;
   run;

proc format cntlin=control;
   run;

proc report data=have spanrows;
   column num variable;
   define num / order f=numord1. order=internal
                style(column)={vjust=c just=c};
   define variable / display;
   run;

The BYTE function creates a hex code. Hex codes from 0 - 31 are not printable so will appear as blanks (some nonprintable characters actually print in some fonts).

This seems like a hack to me so i am hoping someone else will come up with something more robust.

Re: Merge cell vertically in proc report, with missing cells, keep the order of file

SSH2 — Mon, 12 Mar 2018 16:52:27 GMT

Thank you very much!!

I think I found a solution:

data a1;

set a;

if group="" then group= "ZZZ";

run;

data a2;

set a1;

by group notsorted num notsorted;

retain cnt;

if first.group then cnt=0;

cnt+1;

proc report data=a2 spanrows

column group num cnt;

define group / group style(column)={ cellwidth=8in bordercolor=black vjust=c just=l } order=data;

define cnt / order order=internal noprint;

define num / order order=data noprint;

compute group;

if index(group, "ZZZ") then group="";

endcomp;

run;

Thank you very much for your time!!