Pivot table with minimum in SAS

Question

I have a table that looks like this :

data work.Hotels;
Length Hotel_Name $9 Class $1 ;
INPUT Hotel_Name $ Class $ Country $ PRICE ;
CARDS ;
Appolpon A Greece 390
Caravel B Greece 468
Christina A Greece 427
Economy B Greece 369
EdenBeach A Greece 499
HanikianBeach C Greece 526
MarinaBeach C Greece 587
Xenia C Greece 534
Agdal B Maroc 447
Almohades B Maroc 482
Atlas A Maroc 511
AtlasArnadi C Maroc 532
Chems C Maroc 450
Dunes A Maroc 569
AlfaMa B Portu 646
AppDo B Portu 652
DELagos C Portu 802
Madeira A Portu 761
Reid's A Portu 1101
;
run;

I'm trying to produce a table with the countries as Rows, the Class as columns and for each pair, the name of the Hotel with the minimum price. Something like this :

        A           B           C
Greece  Appolpon    Caravel     Marinabeach
Portu   Madeira     Appdo       Delagos
Maroc   Atlas       Agdal       Chems

I tried many things like Proc tabulate and i couldn't get any good results.

Thank you

Answer 1

You will need to pre-process the data so that there is only one value per cell (i.e. per combination of country+class). At that point PROC REPORT with a stacked across column specification will display the names in the cells. A sneaky trick is also needed in order to force REPORT into this layout mode.

Example:

* order data for selecting row of lowest priced county+class;

proc sort data=have out=ordered;
  by country class price name;
run;

* if two lowest prices in a cell are the same select the (alphabetically) first name ;

data lowlowprices;
  set ordered;
  by country class;
  if first.class;  * save first lowest price within the by group;
run;

proc report data=lowlowprices;
  columns country class,name sneaky;  * tip <varname>,<varname> specifies a stacked column ;
  define country / group;
  define class / across;
  define sneaky / noprint;
run;

produces

Excel pivot tablers might pooh-pooh pre-processing data because there are tricks in Excel for what you want done.