Sort Across values the way I want it in proc report

[Eva]

Hi there!

I have a proc report with an across. I'd like the across values to appear in a certain order which is not alpabetical and not like the values appear in the data set.

For a group variable this would be easy because then I would create another group variable 1,2,3,4... with noprint in the define statement and so my grouping variable would habe the order I want. Yet with across that's not so easy because I didn't find a way with a norpint across variable to do this. Maybe I cannot figure out how to do this in the column statement. Stacking for example doesnt work:

column var1 myvalue myacrossnoprint, myacross

define var1 / group....;
define myvalue / analysis sum...;
define myacrossnoprint / across noprint ....;
define myacross / across...;

Does anybody know whether I could solve the task in this way? Or is that way impossible?

I tried another way: I defined a notsorted format:

proc format;
value $myformat (notsorted)
'z' = 'a'
'a' = 'b'
'm' = 'c'
...... ;
run;

And assigened it like this to the across variable:

column var1 myvalue myacross

define var1 / group....;
define myvalue / analysis sum...;
define myacross / across preloadfmt order=data format=$myformat...;

That's great but then all the values written down in the format ar shown even if there is no datarow for it in the data. Now what I'd like to have is that the order defined in the format is kept in the across and that only the values that appear on the dataset are used and displayed. Is that possible?

Best regards
Eva

[Cynthia_sas]

Hi:
When you use PRELOADFMT with a user-defined format, you are essentially asking PROC REPORT to behave as you described -- it will use the format to put all the -possible- values on the report, whether data exists for that particular group or order or across variable or not.

Another approach might be to use ORDER=DATA -- which respects the internal order of the data. This means that you would have to sort the data by your desired order before the PROC REPORT step. The quickest way to do this is make a temporary data set with a variable to ensure the order. Sort of like your idea with NOPRINT, only you're using PROC SORT to presort in your desired order. Test program and Listing output below (using SASHELP.SHOES).

cynthia

[pre]
data shoes;
set sashelp.shoes;
where region in ('Asia', 'Canada', 'Pacific', 'Western Europe');
if region = 'Pacific' then accord=1;
else if region = 'Asia' then accord=2;
else if region = 'Western Europe' then accord=3;
else accord=4;
run;

proc sort data=shoes;
by accord region product;
run;

options nocenter nodate nonumber;
ods listing;
ods html file='c:\temp\ord_across.html' style=sasweb;
proc report data=shoes nowd;
title '1) Using ORDER=DATA';
column product sales,region sales=stot;
define product / group;
define region / across order=data ;
define sales / sum ' ';
define stot / 'All Regions Total';
rbreak after / summarize;
run;
ods html close;

************************* OUTPUT ************
1) Using ORDER=DATA


Region
Western All Regions
Product Pacific Asia Europe Canada Total
Boot $123,575 $62,708 $296,031 $385,613 $867,927
Men's Casual $662,368 $11,754 $946,248 $441,903 $2,062,273
Men's Dress $426,191 $119,366 $747,918 $920,101 $2,213,576
Sandal $48,424 $8,208 $11,349 $14,798 $82,779
Slipper $390,740 $152,032 $857,298 $952,751 $2,352,821
Sport Shoe $26,169 $2,092 $201,030 $140,389 $369,680
Women's Casual $219,886 $25,837 $985,647 $410,807 $1,642,177
Women's Dress $399,441 $78,234 $827,479 $989,350 $2,294,504
$2,296,794 $460,231 $4,873,000 $4,255,712 $11,885,737
[/pre]

[Eva]

Hello Cynthia,

thanx for your reply. That works well!

Best regards,
Eva