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;
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;
You have provided code that has several syntax errors. Please fix those.
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;
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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.