Proc Report ordering

[mark_svensson]

Hi,

First time using PROC REPORT and I have not understanding the sort order.

Under PROC SQL it is something like:

select * from table1 order by colX asc, colY desc

In PROC REPORT, I guess you use

define colX  / display order order=data ;

define colY  / display order order=data descending ;

I have two questions:

How do you asign an overall order to the definition?  ie, I need it order by colX first and then colY

How do I include display data with null values in the order column?  Once you put the word order in the define line, the row no longer appears in the output.

Many thanks

Mark

[arodriguez]

Hi Mark,

You could first of all, do a proc sort by colX and ColY. After that, you could create a new variable order=_N_.

Doing this you create a non missing variable to order data. adding to columns this variable and a definition like

define Order /noprint order

you should see your data as you want.

[RW9]

The order of the columns is defined by the columns statement:

proc report...;
  column var1 var2;
  define var1 / order;
  define var2 / order;
run;

In this example the data is sorted by var1 and then var2, you would swap them over by changing the position in the column.  Per the documentation.  Here is an example:

http://support.sas.com/resources/papers/proceedings11/090-2011.pdf

[Cynthia_sas]

Hi:
And, in addition to RW9's description, there are options that you can specify for both ORDER and GROUP items that will further control the order:
ORDER=DATA (good for pre-sorted rows)
ORDER=INTERNAL (good for dates)
ORDER=FORMATTED
ORDER=FREQ

And to get a column in DESCENDING order, you specify that option on the DEFINE statement, as well.

The COLUMN statement, the DEFINE statement and the usages on the DEFINE statement are very important to understand. For example, it is not appropriate to have both DISPLAY and ORDER on your DEFINE statement -- PROC REPORT will use the last usage you specify -- so if you had reversed the order of the usage option then your DEFINE statement would be broken. Understand the usage (DISPLAY, ORDER, GROUP, ANALYSIS, ACROSS) and specify only 1 usage on a DEFINE statement. The exception to that rule is for numeric variables where this is OK:
define numvar / analysis sum;
or
define numvar2 / analysis mean;

But, in fact, the alternate syntax is fine:
define numvar / sum;
define numvar2 / mean;

Here is a documentation example on ordering the rows in PROC REPORT:
http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#n1jfpsg4rtte8bn1bg958...

cynthia

[mark_svensson]

Hi,

Thank you all for your help.

What I was trying to do was to format a pdf output.  I was looping though multiple clients and creating five subtables for each client.  Each pdf report ran up to 4000 pages.

I ended up using proc sql instead.  It is much easier to write and once I realised you could give a different class to each table, the pdf now looks very colourful and easy to read.

Cheers

Mark

[RW9]

In which case by group processing is what you want:

proc report data=...;
  by client subgroup;
  title1 "Client=#byval1";
  title2 "Subgroup=#byval2";
...
run;

Does all that looping for you.  Only time I loop myself is if there are complicated by groupings and such like.  

[mark_svensson]

Thank you for your reply.  I have not used the approach you mentioned, is it possible to write a little more pseudo code for me?

At the moment I am using a macro to loop through the clients

Then I draw data from five separate tables for each client – move to the next client, etc.

The recipients of the report insist each of the five tables have a different format (colour, font) so it is easier for them to delineate.

The whole report ends up as a large pdf.

Cheers