how to transpose the following table without using proc transpose?

[Jonathanzz]

I want to transpose the following table without using proc transpose:

Original Table:

ID facility

1  Swimming Pool

1  SPA

2  Gym room

2  restaurant

2  bar

3  Room

Expected Table:

ID    facility_1               facility_2       facility_3

1      Swimming Pool    SPA              

2      Gym room            restaurant     bar

3      Room

The number of facilities for each ID is unknown.

Can someone please help me out?

[gamotte]

What's wrong with proc transpose ?

[Jonathanzz]

I am going to transpose the dataset inside SAS enterprise miner using code editor, Howeve, it seems SAS enterprise miner does not support proc transpose.

[gamotte · Accepted solution]

data have;
    length facility $20.;
    infile cards dlm=',' dsd;
    input ID facility;
    cards;
1,Swimming Pool
1,SPA
2,Gym room
2,restaurant
2,bar
3,Room
    ;
run;

proc sql noprint;
    SELECT max(nfac) INTO :nfac TRIMMED
    FROM (
    SELECT count(facility) AS nfac
    FROM have
    GROUP BY ID
    )
    ;
quit;

data want;
    set have;
    by ID;
    retain facility1-facility&nfac.;

    array fac(&nfac.) $20. facility1-facility&nfac.;

    if first.ID then do;
        call missing(of fac(*));
        i=1;
    end;
    else i+1;

    fac(i)=facility;

    if last.ID;

    drop i facility;
run;

[Jonathanzz]

Thanks Gamotte!
I have one more request. Can it be done without using PROC statement and only using data step since SAS Enterprise Miner do not support any PROC statement.
Thank you so much!!!

[gamotte]

Try replacing the proc sql with the following data step :

data _NULL_;
    set have(keep=ID);
    by ID;
    retain nfac;

    if _N_=1 then nfac=0;

    if first.ID then i=1;
    else i+1;

    if last.ID and i>nfac then do;
        nfac=i;
        call symputx("nfac", nfac);
    end;
run;