topic Re: Missing value in proc report, exact order of input data in ODS and Base Reporting

Missing value in proc report, exact order of input data

SSH2 — Wed, 28 Feb 2018 03:23:12 GMT

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!

Re: Missing value in proc report, exact order of input data

RW9 — Wed, 28 Feb 2018 09:39:53 GMT

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.

Re: Missing value in proc report, exact order of input data

SSH2 — Wed, 28 Feb 2018 13:11:34 GMT

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.

Re: Missing value in proc report, exact order of input data

RW9 — Wed, 28 Feb 2018 14:35:25 GMT

It really should be spanrows:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Merge-cell-and-Background-Color-in-tagsets-ExcelXP/td-p/269256

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

Re: Missing value in proc report, exact order of input data

SSH2 — Wed, 28 Feb 2018 15:49:23 GMT

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;

Re: Missing value in proc report, exact order of input data

RW9 — Wed, 28 Feb 2018 16:08:55 GMT

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.

Re: Missing value in proc report, exact order of input data

SSH2 — Wed, 28 Feb 2018 16:45:36 GMT

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.