Help using Base SAS procedures

proc report: sort by analysis variable stats

Reply
Frequent Contributor
Posts: 100

proc report: sort by analysis variable stats

Experts:
How do I modify the proc report code below to show the result sorted by Total Amount descending? I tried to define Amount as a group or order variable, but those two attempts didn't work.

Thank you!

data work.test;
input Fruit$ Amount;
datalines;
Apple 100
Kiwi 250
Apple 100
Pear 50
Kiwi 300
;
run;
proc print data=work.test;
run;
proc report data=work.test nowd;
column fruit amount;
Define fruit / group;
run;
SAS Super FREQ
Posts: 8,868

Re: proc report: sort by analysis variable stats

Hi:
It's not clear, do you want a "detail" report or a "summary" report. A detail report will contain 1 report row for every observation in the dataset; a summary report will contain summarized report rows for every group of observations in the dataset. Either type of report, detail or summary report, can have additional subtotal and grand total report lines.

For example in the sample output below, the Option 1 output has 5 report rows (1 for each observations in the data) and 1 grand total report row. While Option 2 output has 3 report rows (1 for each type of fruit) and 1 grand total report row.
[pre]
Option 1: Detail Report in Descending Order with Total
Fruit Amount
Kiwi 300
Kiwi 250
Apple 100
Apple 100
Pear 50
Total 800

Option 2: Summary Report in Descending Order with Total
Fruit Amount
Kiwi 550
Apple 200
Pear 50
Total 800
[/pre]

Which of these reports do you want? Do you want a grand total summary line?? If you use AMOUNT as a GROUP or ORDER item on your report, you will be instructing PROC REPORT to treat AMOUNT like a category...in that case, the observation with 300 for Kiwi would be considered a different category than the observation with 250 for Kiwi -- is this what you want? Or do you want to be able to perform summarizing with AMOUNT???

cynthia
Frequent Contributor
Posts: 100

Re: proc report: sort by analysis variable stats

Posted in reply to Cynthia_sas
Thank you. Option 2 is what I need. I want to know how to create summary report and sort the results by the analysis variable (e.g. by the total amount for each fruit type).
SAS Super FREQ
Posts: 8,868

Re: proc report: sort by analysis variable stats

Hi:
The trick with PROC REPORT is that you cannot use AMOUNT for a numeric purpose and for an ORDERing purpose.Usage specifications in PROC REPORT determine whether a variable can be summarized or not. If AMOUNT is a GROUP or ORDER variable, then the AMOUNT value ceases to be available for addition or summarization.

And, on top of that, PROC REPORT only writes one report row at a time. So, when PROC REPORT is writing the summary row for Apple, it has no visibility of the summary amounts for Kiwi or Pear. PROC REPORT only writes 1 report row at a time and if it needs to do a summarizing break (subtotal or grand total) at the end of a group or the end of the report, that break line is put on the report at the right location. If AMOUNT is a GROUP or ORDER variable, it would not be available at the break, either, for summarizing purposes. But, there is a way to deal with this situation. You have to make sure that PROC REPORT knows what the report order is -- either by creating an ordering variable or by pre-summarizing the data.

So, you have to make 2 passes through the data, somehow in order to make/create a new variable that can be used for ordering. You could pre-summarize the data with PROC TABULATE, PROC MEANS, even PROC REPORT or PROC SQL. These previous forum postings had similar questions:
http://support.sas.com/forums/thread.jspa?messageID=17088䋀
http://support.sas.com/forums/thread.jspa?messageID=6757ᩥ

The program below uses a PROC SQL to create a new variable called ORDVAR in Approach #1. The code shows ORDVAR on the actual report, but once you understand how ORDVAR is working, you can put the NOPRINT option on the DEFINE statement for ORDVAR in order to use it, but hide it, on the final report. The original number of observations for WORK.TEST was preserved in Approach 1, which might be necessary, if you needed the detail rows or detail observations to do other types of processing or if you wanted a DETAIL report (#1b) as well as a SUMMARY report. Other approaches, such as PROC SQL, PROC MEANS or the PROC TABULATE approach would result in a completely summarized dataset (as shown in Approach #2).

cynthia
[pre]
** Approach 1: Put ORDVAR on every row, where ORDVAR is the total # for the group;
** Descending order happens in PROC REPORT step;
proc sql;
create table work.ordfruit as
select sum(amount) as ordvar, fruit, amount
from work.test
group by fruit;
quit;

proc print data=work.ordfruit;
title 'What does ORDVAR look like on every row';
run;

proc report data=ordfruit nowd;
title '1a) PROC REPORT Summary report showing ORDVAR';
title2 'Use the NOPRINT option to hide ORDVAR';
column ordvar fruit amount;
define ordvar / group descending;
** define ordvar / group descending noprint;
define fruit / group;
define amount / sum;
rbreak after / summarize;
compute after;
fruit = 'Total';
endcomp;
run;

proc report data=ordfruit nowd;
title '1b) PROC REPORT Detail report showing ORDVAR';
column ordvar fruit amount;
define ordvar / order descending noprint;
define fruit / order;
define amount / sum;
rbreak after / summarize;
break after fruit / summarize;
compute after fruit;
line ' ';
endcomp;
compute after;
fruit = 'Total';
endcomp;
run;


** Approach 2: completely summarize and order work.test;
** Descending order happens in PROC SQL step;
** PROC REPORT respects the order with ORDER=DATA;
proc sql;
create table work.sumfruit as
select fruit, sum(amount) as totamt
from work.test
group by fruit
order by totamt descending;
quit;

proc print data=work.sumfruit;
title 'what does summary data look like';
run;

proc report data=sumfruit nowd;
title '2) PROC REPORT Summary report using "pre-summarized" data';
column fruit totamt;
define fruit / order order=data;
define totamt / sum;
rbreak after / summarize;
compute after;
fruit = 'Total';
endcomp;
run;
[/pre]
Frequent Contributor
Posts: 100

Re: proc report: sort by analysis variable stats

Posted in reply to Cynthia_sas
Wow. Thank you for all the details! I know proc report in this example might not be the most efficient way to go but I've learned so much from the discussion about proc report.
SAS Super FREQ
Posts: 8,868

Re: proc report: sort by analysis variable stats

If you want to order by the summarized AMOUNT total, then you're going to have to make 2 passes through the data one way or the other. If there is a valid report reason for needing to do this, then in my way of thinking, you'd pick the report approach that best creates the FINAL output you want. If efficiency is your major concern, then you'll have to write, benchmark and test all the possible different solutions on the same size subset of data to see which approach is most efficient.

In my experience, however, "report cosmetics" or "ease of code maintenance" can overcome efficiency in picking an approach.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 1191 views
  • 0 likes
  • 2 in conversation