Order values of Proc report by user defined format

[Ronein]

Hello

Lets say that I want to use proc report to create a "display Report" (not summary calculations).

Lets say that I want to order the rows in the following way:

First order the rows by the order appears in user defined format Fmt related to column "make",

then order the rows by the alphabetically order of column "Model".

What is the way to do it please ?

Proc Format;
Value $Fmt
'Acura'=1
'Audi'=4
'Honda'=3
'BMW'=2
;
Run;

proc report data=sashelp.cars contents="All Cars" 
Where Make in ('Acura','Audi','Honda','BMW');
spanrows;  
define Make / order; 
define Model / order;    
column Make Model MSRP MPG_City Horsepower;  
run;

[PaigeMiller]

You have provided code that has several syntax errors. Please fix those.

[Ksharp · Accepted solution]

Proc Format;
Value $ Fmt(notsorted)
'Acura'='1'
'Audi'='4'
'Honda'='3'
'BMW'='2'
;
Run;

proc report data=sashelp.cars contents="All Cars" nowd spanrows;
Where Make in ('Acura','Audi','Honda','BMW') ;  
column Make Model MSRP MPG_City Horsepower;  
define Make /group order=data preloadfmt format=$fmt.; 
define Model / order;    
run;

[Ronein]

Thanks,
May you please explain:
Why did you use order=data with preloadfmt ?
Why order=data alone (without preloadfmt ) will not work well?
Why order=formatted will not work and you used order=data?

[PaigeMiller]

@Ronein 

would you please explain something to me? Thanks.

My question is why would you want to format "Audi" which has a clear meaning to people familiar with cars, to have appear in reports as 4 which has no meaning whatsoever to anyone? Exactly what is the purpose of doing this here? It seems as if this could only cause confusion, whereas leaving it as "Audi" confuses no one.

[Ronein]

Good question,
My question was just a theoretical question and there is no meaning . In real life I had cases when I should sort the report by business criteria and here my question come from .

[PaigeMiller]

Even in "theoretical questions", avoiding doing things which, as you say, "have no meaning", is a good habit to develop.

How about this:

data intermediate;
    set sashelp.cars;
    if make='Acura' then order=1;
    else if make='Audi' then order=4;
    else if make='Honda' then order=3;
    else if make='BMW' then order=2;
run;
proc sort data=intermediate;
    by order;
run;
proc report data=intermediate contents="All Cars" spanrows;  
    Where Make in ('Acura','Audi','Honda','BMW');
    column Make Model MSRP MPG_City Horsepower;  
    define Make / order order=data; 
    define Model / order;    
run;

[Ksharp]

Answer is simple .If you want make it happen in this scenoria , you need :

(notsorted) + order=data + preloadfmt


Usually , you only need "(notsorted) + order=data " . But I don't know why also need "preloadfmt" .

[FreelanceReinh]

I think without "preloadfmt" the "order=data" option prevails, i.e., the (formatted) MAKE values in the report occur in the same order as they are found (unformatted) in the input dataset.