Pivot-table with minimum

[jo_pariseau]

I have a table that looks like this :

data work.hotel;
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
AbouSofiane A Tunis 434
Asdrubal A Tunis 489
Colombes B Tunis 436
Darnaour B Tunis 399
Djerba C Tunis 477
Mimosa C Tunis 375
Omarit B Tunis 486
;
run;

I'm trying to create a table with the countries as rows, the class as column and the name of the hotel with the minimum price for each pair.

Something like this :

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

I tried with proc tabulate and proc transpose but couldn't get exactly the result i'm looking for.

Thank you for your help 

[Reeza · Accepted solution]

Take the top record first and then try your transpose. 

1. Sort by Country, class, price
2. Sort by Country, Class - NODUPKEY which will keep the top record
3. Use PROC TRANSPOSE to flip it wide. 

Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

---

@jo_pariseau wrote:

I have a table that looks like this :

data work.hotel;
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
AbouSofiane A Tunis 434
Asdrubal A Tunis 489
Colombes B Tunis 436
Darnaour B Tunis 399
Djerba C Tunis 477
Mimosa C Tunis 375
Omarit B Tunis 486
;
run;

I'm trying to create a table with the countries as rows, the class as column and the name of the hotel with the minimum price for each pair.

Something like this :

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

I tried with proc tabulate and proc transpose but couldn't get exactly the result i'm looking for.

Thank you for your help 

---

[jo_pariseau]

Thank you, with your advices it finally worked.

[Sajid01]

@jo_pariseau 
Could you mind posting the code that worked?

[jo_pariseau]

proc sort data=lib.hotels2 out=lib.hotels3;
by coutry class price;
run;

proc sort data=lib.hotels3 out=lib.hotels4 NODUPKEY;
by coutry class;
run;

proc transpose data=devoir.hotels3 out=devoir.hotels4;
by country ;
id class;
run;

[Sajid01]

Thanks