The SAS Output Delivery System and reporting techniques

PROC REPORT Column Order

Reply
Occasional Contributor
Posts: 9

PROC REPORT Column Order

PROC REPORT:

Does the order of the variables listed in the COLUMN statement and/or the order in which i use the define statement have any impack on compute blocks?


Super User
Posts: 19,770

Re: PROC REPORT Column Order

Yes it has an impact.

From the docs:

PROC REPORT assigns values to the columns in a row of a report from left to right. Consequently, you cannot base the calculation of a computed variable on any variable that appears to its right in the report.

Occasional Contributor
Posts: 9

Re: PROC REPORT Column Order

Is there anyway to reorder the columns after all of the calculations and computations are complete

Super User
Posts: 19,770

Re: PROC REPORT Column Order

No idea, but if the report is complicated I generally use a datastep to preprocess the data and then a proc report to display it.


Cynthia Zender has a great paper on creating complex reports in SAS that is worth a read.

SAS Super FREQ
Posts: 8,864

Re: PROC REPORT Column Order

Thanks for the mention! Here's the paper link:

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

(to see the zip file of programs, go to: http://support.sas.com/rnd/papers and look in the section for 2008 papers to find the zip file.

In the paper, Example 3, I think shows how to order the report ROWS (not columns) based on the summary values (such as descending order of total sales for each region), but it does mean making 2 passes thru the data.

However, the OP asked about reordering the COLUMNs -- which seems sort of strange to me. Once the left-to-right order of the items in a COLUMN statement has been set, you cannot change the order without making another pass through the data. So, for example, if you have this:

COLUMN NAME AGE SEX HEIGHT WEIGHT;

for your report, you cannot suddenly "reorder" within the same PROC REPORT step, so that you have NAME after AGE, for example. You could use PROC REPORT with OUT= to capture the results of grouping/summarizing and then use the OUT= dataset for a "final" report with the COLUMNS in a new order, but the original summarizing and break lines were probably based on a certain order to the original COLUMN statement, so I'm having a hard time visualizing what type of report would need to have the compute block, etc and then need to reply the data with the columns in a different order. I think that more info is really needed to give a more complete answer.

cynthia

Occasional Contributor
Posts: 9

Re: PROC REPORT Column Order

Posted in reply to Cynthia_sas

  Let say I have for example

COLUMN NAME SEX MAILED RESPONDED RESP_RATE

And i want a report that shows the response rate by sex, but I want the response rate to be the second column in my data.

SAS Super FREQ
Posts: 8,864

Re: PROC REPORT Column Order

Hi:

I don't understand what you mean. Is RESP_RATE already in your data set or are you calculating it from MAILED and RESPONDED??? There is a HUGE difference between your DATA (in your SAS data set) and your REPORT (what is being created by PROC REPORT). And, it doesn't make sense, in the above COLUMN statement (COLUMN NAME SEX MAILED RESPONDED RESP_RATESmiley Wink for you to have NAME if what you want is a report summarized by SEX.

Because in your data you could have the NAMES in this order:

Alfred

Alice

Barney

Barbara

Carl

Charlie

Diane

David

in which case getting RESP_RATE by SEX will be hard (see report #1 in attached program).

I would expect the order in the COLUMN statement to be:

COLUMN SEX NAME MAILED RESPONDED RESP_RATE;

Using NOPRINT would allow you to make it appear as though your calculated variable was appearing -before- the items that were used to calculate it (see report #4 in the attached program). You seem to use the terms "data" and "report" interchangeably -- what does your program look like, how is your data structured? Is your data already summarized? What usages do you have for SEX, NAME, MAILED, RESPONDED and RESP_RATE in your PROC REPORT code?

You should be able to run the code below and perhaps it will point you in one direction or another to produce your REPORT (not a DATA set).

cynthia

ods listing close;

ods html file='c:\temp\showcalc.html' style=sasweb;

 

proc report data=sashelp.class nowd;

  title '1) Does Not Make sense to have NAME on the report';

  column name sex age height weight calcvar;

  define name / order;

  define sex / order;

  define age / mean;

  define height / mean;

  define weight / mean;

  define calcvar /computed;

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  break after sex / summarize;

  compute after sex;

    line ' ';

  endcomp;

run;

       

proc report data=sashelp.class nowd;

  title '2) Without Name Make More Sense';

  column  sex age height weight calcvar;

  define sex / group;

  define age / mean;

  define height / mean;

  define weight / mean;

  define calcvar /computed;

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  compute after sex;

    line ' ';

  endcomp;

run;

       

proc report data=sashelp.class nowd;

  title '3) Different Report with Sex and Age as GROUP vars';

  column  sex age height weight calcvar;

  define sex / group;

  define age / group;

  define height / mean;

  define weight / mean;

  define calcvar /computed;

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  break after sex / summarize;

  compute after sex;

    line ' ';

  endcomp;

run;

   

proc report data=sashelp.class nowd;

  title '4) Use NOPRINT to hide vars used in CALC';

  column  sex age height weight calcvar height=ht2 weight=wt2;

  define sex / group;

  define age / group;

  define height / mean noprint;

  define weight / mean noprint;

  define calcvar /computed;

  define ht2 / mean 'Height Alias';

  define wt2 / mean 'Weight Alias';

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  break after sex / summarize;

  compute after sex;

    line ' ';

  endcomp;

run;

ods _all_ close;

Ask a Question
Discussion stats
  • 6 replies
  • 1323 views
  • 0 likes
  • 3 in conversation