topic Re: Pivot-table with minimum in SAS Programming

Pivot-table with minimum

jo_pariseau — Fri, 30 Apr 2021 01:12:48 GMT

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

Re: Pivot-table with minimum

Reeza — Fri, 30 Apr 2021 02:17:03 GMT

Take the top record first and then try your transpose.

Sort by Country, class, price
Sort by Country, Class - NODUPKEY which will keep the top record
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

Re: Pivot-table with minimum

jo_pariseau — Mon, 03 May 2021 12:55:14 GMT

Thank you, with your advices it finally worked.

Re: Pivot-table with minimum

Sajid01 — Mon, 03 May 2021 13:07:36 GMT

@jo_pariseau
Could you mind posting the code that worked?

Re: Pivot-table with minimum

jo_pariseau — Mon, 03 May 2021 13:40:27 GMT

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;

Re: Pivot-table with minimum

Sajid01 — Mon, 03 May 2021 13:59:51 GMT

Thanks