Help using Base SAS procedures

Sort order in Proc Report using order=internal

Occasional Contributor
Posts: 16

Sort order in Proc Report using order=internal

I have the following data ;

Obs page id grpn grpc startdy starttm descrip

18 3 25 2 GROUP 2 -1 0:00 YYY
19 3 25 2 GROUP 2 7 16:00 XXX

With the following structure;

# Variable Type Len Format Label

13 descrip Char 150 Description
8 grpc Char 25 Group (character)
6 grpn Num 8 Group (numeric)
2 id Char 4 ID
66 page Num 8
21 startdy Num 8 Start Day
64 starttm Num 8 TIME5.

If I output this using the following proc report

proc report data = xxx split='~' missing spacing = 1 nowindows headline list headskip ;
by page ;
column id grpn grpc startdy starttm descrip ;

define id / order 'ID' style(column)=[just=center cellwidth=.45in] ;
define grpn / order noprint ;
define grpc / order 'Group' style(column)=[just=left cellwidth=0.68in] ;
define startdy / order format=best9. noprint ;
define starttm / order format=time5. noprint ;
define descrip/ order 'Description' style(column)=[just=left cellwidth=1.5 in] flow ;

I get output in the following order;


If I change “format=best9.” in the STARTDY define statement to “order=internal” I get output in the following order;


Can you explain to me why a numeric variable given a numeric format sorts in the opposite order as the same numeric variable given an order=internal instruction?
Super User
Posts: 3,927

Re: Sort order in Proc Report using order=internal

If you check the SAS help for the REPORT procedure you will find that the default sort order if you do not specify it in a DEFINE statement is FORMATTED. So by adding explicitly ORDER=INTERNAL you are changing the type of ordering.
Occasional Contributor
Posts: 16

Re: Sort order in Proc Report using order=internal

But for a numeric variable how can the unformatted values (order=internal) produce a different sort order than the formatted values (format=best9.) if the actual values are all positive and negative integers?
Posts: 9,373

Re: Sort order in Proc Report using order=internal

PROC REPORT is ordering from LEFT to RIGHT -- so STARTDY is ordered -within- all the other variables on your COLUMN statement starting at the left-most variable. So you have THIS for your COLUMN statement:
column id grpn grpc startdy starttm descrip ;

What happens when you remove the NOPRINT from ALL the DEFINE statements -- you should see some difference in what is displayed. Also, you ONLY show 2 variables from the INPUT dataset. When you use ORDER=INTERNAL, you are telling PROC REPORT to respect the order in which it gets the data -- but that order is determined by startdy within grpc within grpn within id -- so this means you must closely look at the order ALL the variables and their order in the INPUT data. Also, in order to use BY PAGE with PROC REPORT, you must have previously sorted your data or else you read the data in BY group order. So, what did the data look like AFTER the sort? Were there other BY variables on your BY statement??

You also have inconsistent syntax in your program. For example, FLOW, HEADLINE, HEADSKIP and SPACING are all LISTING only options. However your use of STYLE= statement options would imply that you are using or planning to use ODS for your output.

The "before" data that you show looks like PROC PRINT output -- do you only have 2 rows of DATA for ID 25??? And is the order you show in the PROC PRINT the EXACT order of the 2 rows for ID 25?? Was this before or after the sort?? Also what is the significance of the -1 for the STARTDY variable that you show on OBS #18??

Occasional Contributor
Posts: 16

Re: Sort order in Proc Report using order=internal

Posted in reply to Cynthia_sas
I apologize for my forum skills (how were you able to copy a monospaced font into your last message?) and the inadequate data.
The actual data is proprietary and I had to dummy it down quite a bit for public viewing.
The output is via ODS RTF and there are a couple of proprietary macro calls before the proc report that set that all up.
There are hundreds of records in the actual data set that sort "as expected" in the PROC REPORT output. There are only 2 records (the ones specified) that do not.
Yes, there are only two observations in the data set where ID=25 and observations 18 and 19 are in the exact order that they appear in the data set after a
proc sort (by page id grpn grpc startdy and starttm), and just before the proc report.
The key to the whole question is the variable STARTDY. In observation 18 it has a value of -1 and in observation 19 it has a value of 7.
Using order=internal in the define statement for STARTDY the "-1" record appears before the "7" record. Substituting "format=best9." or "format=8." for "order=internal" results in the "7" record appearing before the "-1" record in the final output.

Given that STARTDY is numeric and unformatted coming into the proc report what is the explanation for this behavior.
Super Contributor
Posts: 394

Re: Sort order in Proc Report using order=internal

Here is an explanation of the markup used in this forum.

Regarding your question about sorting, when ORDER=INTERNAL you're asking PROC REPORT to sort based on the numeric values of the variable. In that case, -1 < 7 so of course -1 sorts before 7. When you change to FORMAT=best9. and allow the ORDER= to default to FORMATTED, PROC REPORT format the values and produces the character strings "-1" and "7". The rows are then sorted by these character strings. In this case, the character string "7" precedes the character string "-1", so "7" sorts before "-1".

Try this simple experiment:
data test;
n = -1;
c = put(n, best9.);
n = 7;
c = put(n, best9.);
proc sort data=test out=internal;
by n;
proc sort data=test out=formatted;
by c;
title 'sort by numeric value';
proc print data=internal;
title 'sort by formatted value';
proc print data=formatted;

Ask a Question
Discussion stats
  • 5 replies
  • 4 in conversation