BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SSH2
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
SSH2
Obsidian | Level 7

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

6 REPLIES 6
ArtC
Rhodochrosite | Level 12

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;

 

SSH2
Obsidian | Level 7

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!

ArtC
Rhodochrosite | Level 12

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;
SSH2
Obsidian | Level 7

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. 

ArtC
Rhodochrosite | Level 12

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.

SSH2
Obsidian | Level 7

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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