topic Re: Order values of Proc report by user defined format in SAS Programming

Order values of Proc report by user defined format

Ronein — Fri, 08 Jul 2022 15:22:36 GMT

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;

Re: Order values of Proc report by user defined format

PaigeMiller — Fri, 08 Jul 2022 10:06:20 GMT

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

Re: Order values of Proc report by user defined format

Ksharp — Fri, 08 Jul 2022 13:02:11 GMT

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;

Re: Order values of Proc report by user defined format

Ronein — Fri, 08 Jul 2022 15:27:16 GMT

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?

Re: Order values of Proc report by user defined format

PaigeMiller — Fri, 08 Jul 2022 21:22:16 GMT

@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.

Re: Order values of Proc report by user defined format

Ronein — Sat, 09 Jul 2022 00:12:13 GMT

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 .

Re: Order values of Proc report by user defined format

PaigeMiller — Sat, 09 Jul 2022 09:38:16 GMT

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;

Re: Order values of Proc report by user defined format

Ksharp — Sat, 09 Jul 2022 10:19:55 GMT

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" .

Re: Order values of Proc report by user defined format

FreelanceReinh — Sat, 09 Jul 2022 12:12:11 GMT

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.