The SAS Output Delivery System and reporting techniques

Proc Report - Sort descending the latest ACROSS column?

Reply
Contributor
Posts: 51

Proc Report - Sort descending the latest ACROSS column?

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:

Q1Q2Q3
Bluefish523
Goldfish032
Redfish008
558

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

Q1Q2Q3
Redfish008
Bluefish523
Goldfish032
558

All suggestions welcomed!

Thank you

Attachment
Super User
Super User
Posts: 7,955

Re: Proc Report - Sort descending the latest ACROSS column?

Posted in reply to RB1Kenobi

Just presort your data and use order=data:

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

Contributor
Posts: 51

Re: Proc Report - Sort descending the latest ACROSS column?

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?

Super User
Super User
Posts: 7,955

Re: Proc Report - Sort descending the latest ACROSS column?

Posted in reply to RB1Kenobi

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.

Contributor
Posts: 51

Re: Proc Report - Sort descending the latest ACROSS column?

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. 

 

Super User
Posts: 10,028

Re: Proc Report - Sort descending the latest ACROSS column?

Posted in reply to RB1Kenobi

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

Contributor
Posts: 51

Re: Proc Report - Sort descending the latest ACROSS column?

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

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