The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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

[ Edited ]

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: 

VariableGroupNum
drugL 1
drugZ 2
drugCD3
drugDB4
drugEB5
drugFB6
drugXB7
drugHB8
drugIC9
drugJB10
drugKB11
drugLF12

 

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


Accepted Solutions
Solution
‎03-12-2018 12:52 PM
Occasional Contributor
Posts: 12

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

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!!

 

View solution in original post


All Replies
Valued Guide
Posts: 653

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

[ Edited ]

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;

 

Occasional Contributor
Posts: 12

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

[ Edited ]

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!

Valued Guide
Posts: 653

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

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;
Occasional Contributor
Posts: 12

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

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. 

Valued Guide
Posts: 653

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

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.

Solution
‎03-12-2018 12:52 PM
Occasional Contributor
Posts: 12

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

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!!

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 421 views
  • 0 likes
  • 2 in conversation