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!
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!!
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;
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!
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;
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.
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.
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.