02-27-2018 10:23 PM
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:
The ideal output table is attached. Thank you for your help!
02-28-2018 04:39 AM
Not sure that is possible as such. Groups normally appear on the left of the data, e.g;
In fact, unless that table is not being used any further, you might find it causes problems down the line.
02-28-2018 08:11 AM
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.
02-28-2018 09:35 AM
It really should be spanrows:
Can you perhaps describe what "does not work", providing test data/code would be helpful.
02-28-2018 10:46 AM - edited 02-28-2018 10:49 AM
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.
input variable $ @10 groups $1. @13 num;
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
proc report data=have spanrows;
column variable groups num;
define groups/ group;
define num/order noprint;
02-28-2018 11:08 AM
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.
02-28-2018 11:45 AM
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.