BookmarkSubscribeRSS Feed
RB1Kenobi
Quartz | Level 8

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

6 REPLIES 6
RB1Kenobi
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2450 views
  • 6 likes
  • 3 in conversation