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:
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!
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.
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.
It really should be spanrows:
Can you perhaps describe what "does not work", providing test data/code would be helpful.
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;
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.
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.
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.