The SAS Output Delivery System and reporting techniques

Missing value in proc report, exact order of input data

Reply
Occasional Contributor
Posts: 12

Missing value in proc report, exact order of input data

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!

Super User
Super User
Posts: 9,794

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

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.

       

Occasional Contributor
Posts: 12

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

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. 

Super User
Super User
Posts: 9,794

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

 

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.

Occasional Contributor
Posts: 12

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

[ Edited ]

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;

 

Super User
Super User
Posts: 9,794

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

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.

Occasional Contributor
Posts: 12

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

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.

Ask a Question
Discussion stats
  • 6 replies
  • 197 views
  • 0 likes
  • 2 in conversation