topic Re: How to sort computed column variable? in ODS and Base Reporting

How to sort computed column variable?

deleted_user — Wed, 28 Jan 2009 18:19:34 GMT

Can anyone help me if I want to sort a computed column variable in Proc Report? Thanks very much. Message was edited by: htfdct

Re: How to sort computed column variable?

Cynthia_sas — Wed, 28 Jan 2009 20:43:08 GMT

Hi:
You can only "sort" or "order" on group or order variables with PROC REPORT. A report item (what appears in the COLUMN statement) can only have one usage -- that means a usage of COMPUTED for a report item OR a usage of ORDER or GROUP, but not both usages.

Your only choice is to create an output dataset (from PROC REPORT or another procedure) and then do your ordering on the computed variable. This example uses PROC REPORT to calculate projected sales for each product within each region and then uses the projected sales figure to control the order of the products within each region.

The first Proc Report is a summary report (without break lines) because all we want to do is get the projected sales item -and- the useord item for the next PROC REPORT. The next PROC REPORT just has a usage of ORDER for the region, useord and product report items because the data has already been summarized.

You will see, if you run this program, that for every region, the products are now ordered by the descending projected sales -- which happened because the USEORD item appears in the column statement before the PRODUCT item. Since the USEORD item is assigned the NOPRINT attribute, the item will be used for ordering, but will not appear on the final report. The PROJ_SALES item has a SUM usage because we want to summarize it as a result of the BREAK statement.

cynthia
[pre]
proc report data=sashelp.shoes nowd out=work.compord;
where region in ('Asia', 'Africa', 'Canada');
column region product sales proj_sales useord;
define region / group;
define product / group;
define sales / sum;
define proj_sales/computed;
define useord / computed;
compute proj_sales;
proj_sales=sales.sum * 1.15;
endcomp;
compute useord;
useord=proj_sales;
endcomp;
run;

proc report data=work.compord nowd;
column region useord product sales proj_sales;
define region / order;
define useord / order descending noprint;
define product / order;
define sales /sum;
define proj_sales/ sum 'Projected/Sales/at 15%' f=dollar14.;
break after region / summarize;
run;
[/pre]

Re: How to sort computed column variable?

deleted_user — Thu, 29 Jan 2009 02:28:46 GMT

Thank you Cynthia! It was very helpful

Re: How to sort computed column variable?

BigD — Thu, 29 Jan 2009 13:51:09 GMT

Hi,

When I run this example, why are the projected sales not in descending order (or any order)?
The oseord column is in order (remove the no print), but not projected sales.

Region useord Product Total Sales Projected
218970.35 Sandal $190,409 $218,970
137810.25 Boot $119,835 $137,810
647213.1 Men's Casual $562,794 $647,213
480143.4 Women's Casual $417,516 $480,143
430454.2 Women's Dress $374,308 $430,454
387637.4 Slipper $337,076 $387,637
25472.5 Sport Shoe $22,150 $25,473
366275 Men's Dress $318,500 $366,275

Re: How to sort computed column variable?

data_null__ — Thu, 29 Jan 2009 14:34:04 GMT

It is because the decimal point is shifted by BEST format.

I suggest using order=internal.

[pre]
define useord / order descending noprint order=internal;
[/pre]

Re: How to sort computed column variable?

BigD — Thu, 29 Jan 2009 14:59:27 GMT

That's the ticket!

Re: How to sort computed column variable?

Cynthia_sas — Thu, 29 Jan 2009 16:47:10 GMT

Hi:
You don't actually need ORDER=INTERNAL. Just put a format in your PROC REPORT.

Here's the updated code and the results from each step are shown below it (changed the code to get only 2 regions for shorter posting).
cynthia
[pre]
proc report data=sashelp.shoes nowd out=work.compord;
title '1) Make USEORD -- computed columns will be UNORDERED';
where region in ('Asia', 'Africa');
column region product sales proj_sales useord;
define region / group ;
define product / group;
define sales / sum f=17.2;
define proj_sales/computed f=17.2;
define useord / computed f=17.2;
compute proj_sales;
proj_sales=sales.sum * 1.15;
endcomp;
compute useord;
useord=proj_sales;
endcomp;
run;

proc print data=work.compord;
title '1a) Proc Print Before PROC REPORT';
run;

proc report data=work.compord nowd nocenter;
column region useord product sales proj_sales;
title '2) Now ORDER with the USEORD column';
define region / order;
define useord / order descending f=17.2;
define product / order;
define sales /sum f=dollar17.2;
define proj_sales/ sum 'Projected/Sales/at 15%' f=dollar17.2;
break after region / summarize skip;
run;
[/pre]

Output from the 1st PROC REPORT (#1) shows that every region is ordered by the alpha order of the PRODUCT (Boot comes first for both regions):
[pre]
1) Make USEORD -- computed columns will be UNORDERED

Region Product Total Sales proj_sales useord
Africa Boot 119835.00 137810.25 137810.25
Men's Casual 562794.00 647213.10 647213.10
Men's Dress 318500.00 366275.00 366275.00
Sandal 190409.00 218970.35 218970.35
Slipper 337076.00 387637.40 387637.40
Sport Shoe 22150.00 25472.50 25472.50
Women's Casual 417516.00 480143.40 480143.40
Women's Dress 374308.00 430454.20 430454.20
Asia Boot 62708.00 72114.20 72114.20
Men's Casual 11754.00 13517.10 13517.10
Men's Dress 119366.00 137270.90 137270.90
Sandal 8208.00 9439.20 9439.20
Slipper 152032.00 174836.80 174836.80
Sport Shoe 2092.00 2405.80 2405.80
Women's Casual 25837.00 29712.55 29712.55
Women's Dress 78234.00 89969.10 89969.10

[/pre]

Output from the PROC PRINT (#1a) shows that the output dataset is in the same order as created in #1 by REPORT (Boot comes first for every region
-- nothing is ordered with USEORD yet -- although at this point, you could use PROC SORT on the file -- but that's unnecessary since PROC REPORT
will do it in the next step):
[pre]
1a) Proc Print Before PROC REPORT

proj_
Obs Region Product Sales sales useord _BREAK_

1 Africa Boot $119,835 137810.25 137810.25
2 Africa Men's Casual $562,794 647213.10 647213.10
3 Africa Men's Dress $318,500 366275.00 366275.00
4 Africa Sandal $190,409 218970.35 218970.35
5 Africa Slipper $337,076 387637.40 387637.40
6 Africa Sport Shoe $22,150 25472.50 25472.50
7 Africa Women's Casual $417,516 480143.40 480143.40
8 Africa Women's Dress $374,308 430454.20 430454.20
9 Asia Boot $62,708 72114.20 72114.20
10 Asia Men's Casual $11,754 13517.10 13517.10
11 Asia Men's Dress $119,366 137270.90 137270.90
12 Asia Sandal $8,208 9439.20 9439.20
13 Asia Slipper $152,032 174836.80 174836.80
14 Asia Sport Shoe $2,092 2405.80 2405.80
15 Asia Women's Casual $25,837 29712.55 29712.55
16 Asia Women's Dress $78,234 89969.10 89969.10

[/pre]

Output from the 2nd PROC REPORT (#2) shows that the USEORD column is now controlling the output. Essentially, you ignore #1 and #1a in your
final report and only use #2 as the FINAL report. #1 and #1a are how you GET to #2 :
[pre]

2) Now ORDER with the USEORD column

Projected
Sales
Region useord Product Total Sales at 15%
Africa 647213.10 Men's Casual $562,794.00 $647,213.10
480143.40 Women's Casual $417,516.00 $480,143.40
430454.20 Women's Dress $374,308.00 $430,454.20
387637.40 Slipper $337,076.00 $387,637.40
366275.00 Men's Dress $318,500.00 $366,275.00
218970.35 Sandal $190,409.00 $218,970.35
137810.25 Boot $119,835.00 $137,810.25
25472.50 Sport Shoe $22,150.00 $25,472.50
Africa $2,342,588.00 $2,693,976.20

Asia 174836.80 Slipper $152,032.00 $174,836.80
137270.90 Men's Dress $119,366.00 $137,270.90
89969.10 Women's Dress $78,234.00 $89,969.10
72114.20 Boot $62,708.00 $72,114.20
29712.55 Women's Casual $25,837.00 $29,712.55
13517.10 Men's Casual $11,754.00 $13,517.10
9439.20 Sandal $8,208.00 $9,439.20
2405.80 Sport Shoe $2,092.00 $2,405.80
Asia $460,231.00 $529,265.65

[/pre]