proc report order option

KarthikSrivasthav — Fri, 02 Mar 2012 10:47:47 GMT

I want the descending order of values of a computed

Column in proc report

Please help

Advance thanks

proc report order option

Cynthia_sas — Fri, 02 Mar 2012 20:05:24 GMT

Hi:

There have been some previous postings about this.

http://communities.sas.com/message/114654#114654

http://communities.sas.com/message/17088#17088

PROC REPORT does have an ORDER= option -- but you can only use it with GROUP or ORDER items. A COMPUTED usage for an item prevents you from using ORDER= with that item. Ways around this are to "precalculate" the computed value in a data step program or sql query and then use the computed value for ordering with a usage of ORDER or GROUP.

At least one of the previous postings had a code example posted.

cynthia

(See the description of ORDER= in the doc:

http://support.sas.com/documentation/cdl/en/proc/63079/HTML/default/viewer.htm#p0wy1vqwvz43uhn1g77eb5xlvzqh.htm )

proc report order option

KarthikSrivasthav — Sat, 03 Mar 2012 02:35:50 GMT

Hi cynthia

Sorry not proc report option i.e define option

But what u mentioned is we need to compute in previous dataset

that is to be used in proc report but i am caluculating percentage according to zones

If i use break after zone/summarize;the percent is is added up and giving

more than 100%

Re: proc report order option

Cynthia_sas — Sat, 03 Mar 2012 19:43:53 GMT

Hi:

Without seeing your data and your calculations, it's hard to recommend an alternative solution. You can alter the erroneous percent at the break point (such as after zone), or, if you pre-summarize your data and then summarize your percent numbers, you can define your percent to be display or order (since you want to order on it) and then don't worry about it.

But now it seems to me that you have two issues. You have the percent being more than 100% issue and you still want/need to order on the computed variable.

Both of these items, taken together, indicate the need for a change of approach to me. There are ways to solve the percent issue using a COMPUTE block, because you can test the value at the break based on the _BREAK_ automatic variable. If you need to recalculate a summary total or blank it out completely, then you can do that. However, you still cannot use ORDER on a COMPUTED item, so something has to change. Switching to TABULATE might help with the percent issue but then you'd still have the ordering issue. So you may as well stick with PROC REPORT. PROC REPORT can make output datasets -- which will work to your advantage here.

If you make one pass through the data with PROC REPORT, and get the computed numbers in a dataset -- you've been able to use PROC REPORT for doing your computations. Still have the break issue, but you have now both presummarized your data and calculated your percent number. Then, with the pre-summarized data -- if you make a "copy" of the computed variable so you have it for ordering, you can order on the item that was computed in the first pass.

In the program below, PCTGRP is the computed percent for each group and PORD is just a "copy" of that variable. PCTGRP will be used in the FINAL report for summarizing and PORD will be used to order by DESCENDING value of the computed item. Note that for the jobcodes, the first pass report shows F and M rows in the same order for both jobcodes. But in the FINAL report, ordered by descending PORD within JOBCODE, for the AAA jobcode, the M row comes before the F row -- which is descending order.

This is some fake data that illustrates the concept, but depending on your data, you may or may not be able to use this logic out of the box. It really depends on your data. What you want to do (order on a computed item) is possible, and fixing the percent is possible.

cynthia

data work.fakedata;
   keep jobcode gender salary ;
   length jobcode $25 gender $1 Salary 8 ;
   label gender= 'Employee Gender'
         jobcode= 'Job Title'
         Salary= 'Annual Salary';
   format Salary DOLLAR12. ;

infile datalines delimiter=',' dsd;
input jobcode $ gender $ salary;
return;
datalines;
"BBBB BBB","M",32040
"BBBB BBB","F",30890
"AAA AAAA","M",30070
"AAA AAAA","F",28525
"BBBB BBB","M",32490
"AAA AAAA","F",29715
"AAA AAAA","M",29695
"AAA AAAA","M",30265
"AAA AAAA","M",28480
"AAA AAAA","M",29965
"AAA AAAA","F",30490
"AAA AAAA","F",29990
"AAA AAAA","M",36605
"BBBB BBB","F",30765
"AAA AAAA","F",30785
"BBBB BBB","M",30660
"AAA AAAA","M",28135
"AAA AAAA","M",28830
"AAA AAAA","M",28345
"AAA AAAA","F",28745
"AAA AAAA","M",28510
"AAA AAAA","F",28025
"BBBB BBB","F",32470
"AAA AAAA","F",30590
"AAA AAAA","M",29700
"AAA AAAA","F",29725
"BBBB BBB","M",31645
"AAA AAAA","M",30450
"AAA AAAA","M",30610
"AAA AAAA","F",29805
"AAA AAAA","F",28825
"BBBB BBB","F",30170
"AAA AAAA","M",28585
"BBBB BBB","F",31495
"AAA AAAA","M",29340
"AAA AAAA","M",28205
"AAA AAAA","M",30510
"BBBB BBB","M",32770
"BBBB BBB","F",32680
"BBBB BBB","F",31605
"BBBB BBB","F",32270
"BBBB BBB","M",30635
"BBBB BBB","M",31590
"AAA AAAA","F",28135
"AAA AAAA","M",30540
"BBBB BBB","M",32075
"AAA AAAA","M",28635
"AAA AAAA","M",30480
"AAA AAAA","F",29445
"BBBB BBB","M",30340
"AAA AAAA","M",30235
"BBBB BBB","M",31430
"AAA AAAA","F",28460
"AAA AAAA","F",28680
"AAA AAAA","M",28590
"BBBB BBB","F",39185
"AAA AAAA","M",29660
"AAA AAAA","F",28140
"AAA AAAA","F",30620
"BBBB BBB","M",31745
"AAA AAAA","M",30385
"AAA AAAA","M",28310
"AAA AAAA","M",28115
"AAA AAAA","F",28185
"AAA AAAA","M",28120
"AAA AAAA","F",29805
"AAA AAAA","M",28430
"AAA AAAA","M",29490
"AAA AAAA","M",28180
"AAA AAAA","F",30135
"BBBB BBB","M",31920
"AAA AAAA","F",37320
"AAA AAAA","F",30280
"AAA AAAA","M",29860
"AAA AAAA","F",28615
"AAA AAAA","M",30260
"AAA AAAA","M",28955
"AAA AAAA","M",29360
"AAA AAAA","M",29995
"AAA AAAA","M",29920
"AAA AAAA","M",29275
"AAA AAAA","F",29500
"AAA AAAA","M",28735
"AAA AAAA","F",30210
"AAA AAAA","F",29555
"AAA AAAA","F",30525
"AAA AAAA","F",30040
"BBBB BBB","M",30655
"AAA AAAA","M",30460
"AAA AAAA","M",29235
"AAA AAAA","M",29445
"BBBB BBB","F",32015
"BBBB BBB","M",32355
"BBBB BBB","M",30150
"AAA AAAA","M",30320
"AAA AAAA","M",28115
"AAA AAAA","M",28985
"AAA AAAA","F",28430
"AAA AAAA","M",29920
"AAA AAAA","M",28365
"AAA AAAA","F",31275
"BBBB BBB","F",31515
"AAA AAAA","M",28665
"BBBB BBB","M",31765
"AAA AAAA","F",34060
"BBBB BBB","F",30895
"BBBB BBB","M",30765
"AAA AAAA","M",29505
"AAA AAAA","F",28460
"BBBB BBB","F",30565
"AAA AAAA","F",29540
"BBBB BBB","F",40755
"AAA AAAA","F",30425
"BBBB BBB","F",31615
"AAA AAAA","F",29045
"AAA AAAA","F",28435
"BBBB BBB","F",31105
"AAA AAAA","M",29920
"AAA AAAA","M",28330
"AAA AAAA","F",28150
"AAA AAAA","M",29370
"AAA AAAA","M",29435
"AAA AAAA","F",29520
"AAA AAAA","M",28580
"AAA AAAA","M",29195
"BBBB BBB","F",30650
"AAA AAAA","F",30770
"AAA AAAA","M",29640
"AAA AAAA","M",30410
"AAA AAAA","M",28200
"BBBB BBB","M",31080
"AAA AAAA","M",29670
"BBBB BBB","F",30615
"BBBB BBB","F",32165
"BBBB BBB","F",32745
"BBBB BBB","F",32475
"BBBB BBB","F",30380
"AAA AAAA","M",30410
"BBBB BBB","M",32775
"BBBB BBB","M",31320
"BBBB BBB","F",31750
"BBBB BBB","F",32985
"BBBB BBB","M",32210
"BBBB BBB","M",31515
"AAA AAAA","M",28060
"BBBB BBB","M",31335
"AAA AAAA","F",29775
"AAA AAAA","M",28310
"AAA AAAA","F",29525
"AAA AAAA","M",28845
"AAA AAAA","F",29955
"AAA AAAA","M",29805
"AAA AAAA","M",30185
"AAA AAAA","F",28800
"AAA AAAA","M",29815
"BBBB BBB","F",30305
"AAA AAAA","F",28040
"BBBB BBB","F",31865
"AAA AAAA","F",29385
"AAA AAAA","M",28625
"AAA AAAA","M",28585
"AAA AAAA","F",30235
"AAA AAAA","M",28510
"BBBB BBB","M",32235
"BBBB BBB","M",32725
"AAA AAAA","F",29545
"BBBB BBB","F",31380
;
run;

ods listing close;
options nocenter noquotelenmax;

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

proc report data=fakedata nowd out=work.pass1
    style(summary)=Header;
    title 'First Pass shows how PROC REPORT';
    title2 'Calculated with Temporary Variables';
    title3 'Only used breaks here to show PCTGRP calcs are wrong at _RBREAK_';
    column jobcode
           gender salary PctGrp pord
           salary=psal;
    define jobcode / group 'Job Title' f=$25.;
    define gender / group 'Gender';
    define salary / sum 'Salary' f=comma14.;
    define pctgrp / computed 'PctGrp' f=percent9.2;
    define pord / computed;
    define psal / pctsum 'PSal' f=percent9.2;
    break after jobcode / summarize;
    rbreak after / summarize;

    compute before jobcode;
      grptot=salary.sum;
    endcomp;
    compute PctGrp;
       PctGrp=salary.sum / grptot;
    endcomp;
    compute pord;
      pord=pctgrp;
    endcomp;
    compute after jobcode /
       style=Header;
      line ' ';
    endcomp;
    compute after;
      jobcode = 'Overall Report';
    endcomp;

run;

proc print data=work.pass1;
where upcase(_break_) not in ('JOBCODE', '_RBREAK_');
title 'pass1: get computed PCTGRP and other vars';
title2 'ONLY need numbers, do not need any break lines';
format pctgrp psal pord best10.2;
run;


** sort by descending computed item;
proc sort data=work.pass1 out=work.final;
by jobcode descending pord;
where upcase(_break_) not in ('JOBCODE', '_RBREAK_');
run;

** create final report;
** after running once, then uncomment the NOPRINT for PORD;
** use ORDER=DATA for PORD to preserve DESCENDING order;
options missing = ' ';
proc report data=work.final nowd
     style(summary)=Header;
title 'Final PROC REPORT ordered by DESCENDING PctGRP value (PORD)';
title2 'Use the NOPRINT option to hide PORD';
column jobcode pord gender salary pctgrp psal;
define jobcode / order 'Job Title' f=$25.
         style(column)=Header;
define pord / order order=data /* noprint */
         style(column)=Header ;
define gender / order 'Gender'
         style(column)=Header;
define salary / sum 'Total Salary' f=comma14.;
define pctgrp / sum 'PctGrp' f=percent9.2;
define psal / sum 'PSal' f=percent9.2;
break after jobcode / summarize;
compute after jobcode;
    line ' ';
endcomp;
rbreak after / summarize;
compute pctgrp;
    if _break_ = '_RBREAK_' then do;
       ** could also just blank out the value;
       ** pctgrp.sum = .;
       ** this calc will make PCTGRP 100%;
       pctgrp.sum = salary.sum/salary.sum;
    end;
endcomp;
compute after;
    ** now adjust the summary label;
    jobcode = 'Overall Total';
endcomp;
run;

ods _all_ close;
ods listing;

topic Re: proc report order option in SAS Procedures

proc report order option

proc report order option

proc report order option

Re: proc report order option