How to control the order in proc report

[Coooooo_Lee]

Hi,

Thanks very much if anyone can help to resolve below issue.

I've got a problem regarding the proc report. Below are the test code and result.

data test;
  input c1 $
	    c2 $
	    c3 $
	    c4 $
	    c5 $;
datalines;
A 1001 70/M C3D1 1
A 1001 70/M C3D1 2
A 1001 70/M C5D1 1
A 1001 70/M C5D1 2
A 1001 70/M C7D1 1
A 1001 70/M C7D1 2
A 1002 68/F SCR  1
A 1002 68/F SCR  2
A 1002 68/F C3D1 1
A 1002 68/F C3D1 2
A 1002 68/F C5D1 1
A 1002 68/F C5D1 2
;
RUN;



PROC REPORT DATA=TEST NOWD HEADLINE HEADSKIP SPLIT='#' SPACING=1 MISSING ;
column c1 c2 c3  c4  c5;  
 define c1 /  'C1' order order=data;

  define c2 /  'C2' order order=data ;

  define c3 /  'C3' order order=data;

  define c4 /  'C4'  order order=data ;

  define c5 /  'C5';
RUN;

Why  order in the result is different from the dataset?

I've set each order option as oder=data.

For the 1002, the records when C4="SCR"  go after the C3D1, how can I ouput the order the same as datasets?

[FreelanceReinh · Accepted solution]

Hi @Coooooo_Lee,

---

@Coooooo_Lee wrote:

Why  order in the result is different from the dataset?

I've set each order option as oder=data.

---

This is because ORDER=DATA looks at the entire dataset. Since C4 values "C3D1", "C5D1" and "C7D1" occur before "SCR" in the data (for a different C2 value, though, but this doesn't matter), this order is used consistently throughout the report.

Basically the same issue was discussed earlier this year in the thread "PROC REPORT order=data within levels of grouping variable". The solution provided there should work for your data, too.

Edit:

Here's how you could implement that solution:

data want;
set test;
by c1-c4 notsorted;
realorder+first.c4;
run;

proc report data=want headline headskip split='#' spacing=1 missing;
column c1-c3 realorder c4 c5;
define c1 /  'C1'  order order=data;
define c2 /  'C2'  order order=data;
define c3 /  'C3'  order order=data;
define realorder / order noprint;
define c4 /  'C4'  order order=data;
define c5 /  'C5';
run;