BookmarkSubscribeRSS Feed
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. keep the exact order of input data set in the Proc report output

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!

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not sure that is possible as such.  Groups normally appear on the left of the data, e.g;

Group  Variable

---

            drugl

            drugz

---

d          drugc

---

b          drugd

            druge

            drugf

...

 

In fact, unless that table is not being used any further, you might find it causes problems down the line.

       

SSH2
Obsidian | Level 7

Thank you for your time. It doesn't matter "Group" on the left or right.  This proc report output table is a final report table. I tried to use spanrows but does not work. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

It really should be spanrows:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Merge-cell-and-Background-Color-in-tagsets-Exc...

 

Can you perhaps describe what "does not work", providing test data/code would be helpful.

SSH2
Obsidian | Level 7

Thank you. My code does not create the desired table.  First I don't want to merge the cells with missing value. Second, I want the proc report table has the exact order of the input dataset.  Third, the goal is to merge the cells if the "num" values are adjacent.

Here is the code and the desired table is attached.

data have;

input variable $ @10 groups $1. @13 num;

datalines;

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

run;

proc report data=have spanrows;

column variable groups num;

define groups/ group;

define num/order noprint;

run;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I am afraid I do not know of a way, you either group the data, or you don't group the data.  Missing is a group, you could theoretically put non-printing spaces in there to make the two different, but why, doesn't make sense.

You "data in the order of the dataset" also doesn't make sense, if B is a group, then that group includes all values with B, otherwise it is not a group, why shouldn't drugJ appear in the same group as drug x, they are both group a.  For example, drugs are normally coded to ATC, and then grouped under the various levels of that.

SSH2
Obsidian | Level 7

Thank you. I don't worry whether or not the desired table makes sense or not. I am more interested in how to get the desired format.  The desired table will keep the same order of the input dataset. I am trying to play with proc report to reach the goal. Not to understand why we want to do that.  Thank you anyway.

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
  • 931 views
  • 0 likes
  • 2 in conversation