Proc Report - Sort descending the latest ACROSS column?

[RB1Kenobi]

Hello

Using Proc Report is it possible to specify an across variable (e.g. Sales Quarter) and then order the resulting output by the most recent column?   I would like to avoid extra datasteps (e.g. prior summarizing or involving OUT= and then specifying a sort on a _C_ column) so that the sort occurs within the REPORT procedure itself

My existing output is:

	Q1	Q2	Q3
Bluefish	5	2	3
Goldfish	0	3	2
Redfish	0	0	8
	5	5	8

My desired output is, where the results are sorted by Q3 sales:

	Q1	Q2	Q3
Redfish	0	0	8
Bluefish	5	2	3
Goldfish	0	3	2
	5	5	8

All suggestions welcomed!

Thank you

[RW9]

Just presort your data and use order=data:

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

[RB1Kenobi]

That's a useful paper, thanks.  My attempts with the demo code (attached to the original post) are not working as expected.  I think because the data contains a counter, so to apply a sort it would have to be summed prior to the report procedure?  Would you be able to show it using my demo data please?

[RW9]

There are several summing functions available, proc summary, means, freq, SQL count etc.  An example: https://communities.sas.com/message/164729#164729

Just get your data looking as you want before sending into the proc report.

[RB1Kenobi]

Thanks RW9.  However I'm basically trying to grow my proc report knowledge specifically to avoid the need to summarize the input data (less coding, perhaps less CPU, shorter program etc..).  I was really looking to resolve the sort within the reporting procedure (if this is actually possible), it's just the ACROSS element that seems to prevent me ordering as I would like to. 

[Ksharp]

I am afraid proc report don't have such power . must add some code to pre-order or pre-process data according to order=data .

or you could post it at ODS and Base Reporting  Cynthia@sas might have some good idea.

data demo_data;
infile datalines delimiter=',';
input transaction_date $ transaction_type$  product $ sale_counter;
datalines;
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Redfish,1
Q3, Sale, Bluefish,1
Q3, Sale, Bluefish,1
Q3, Sale, Bluefish,1
Q3, Sale, Goldfish,1
Q3, Sale, Goldfish,1
Q2, Sale, Bluefish,1
Q2, Refund, Goldfish,.
Q2, Sale, Bluefish,1
Q2, Refund, Goldfish,.
Q2, Sale, Goldfish,1
Q2, Sale, Goldfish,1
Q2, Sale, Goldfish,1
Q2, Refund, Redfish
Q2, Refund, Redfish
Q2, Refund, Redfish
Q1, Sale, Bluefish,1
Q1, Refund, Goldfish,.
Q1, Sale, Bluefish,1
Q1, Refund, Goldfish,.
Q1, Sale, Bluefish,1
Q1, Refund, Goldfish,.
Q1, Sale, Bluefish,1
Q1, Refund, Goldfish,.
Q1, Sale, Bluefish,1
Q1, Refund, Goldfish,.
Q1, Sale, Bluefish,1
Q1, Refund, Goldfish,.
;
run;

* The output I desire would sort the output by Q3 sales descending, so Redfish would be top of the list;

proc sql;
 create table work.want(drop=x y)     as
  select *,input(compress(transaction_date, ,'kd'),best32.) as x,count(*) as y
   from  work.demo_data
     group by  transaction_date, Product
       order by  x desc,y desc;
quit;

ods listing close;
ods html file='x.html' ;
proc report data= work.want nowd ;
     title1 'Sales by Quarter';
     column ('' Product) ('' transaction_date), (sale_counter) ;
     define transaction_date / '' across ;
     define product / group '' order=data;
     define sale_counter / analysis sum '' ;

     rbreak after / summarize;
run;
ods html close;
ods listing;

Xia Keshan

[RB1Kenobi]

Thanks Xia for your example.   I'll try the ODS group.  If it's not possible then that is good to know.