BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User
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
Meteorite | Level 14
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
Diamond | Level 26

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

--
Paige Miller
Ronein
Meteorite | Level 14
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
Diamond | Level 26

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;
--
Paige Miller
Ksharp
Super User
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
Jade | Level 19

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 760 views
  • 2 likes
  • 4 in conversation