BookmarkSubscribeRSS Feed
mark_svensson
Calcite | Level 5

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

6 REPLIES 6
arodriguez
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Super FREQ
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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1536 views
  • 1 like
  • 4 in conversation