topic Re: how to transpose the following table without using proc transpose? in SAS Programming

how to transpose the following table without using proc transpose?

Jonathanzz — Thu, 18 Apr 2019 08:51:38 GMT

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?

Re: how to transpose the following table without using proc transpose?

gamotte — Thu, 18 Apr 2019 09:26:12 GMT

What's wrong with proc transpose ?

Re: how to transpose the following table without using proc transpose?

Jonathanzz — Thu, 18 Apr 2019 09:37:12 GMT

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.

Re: how to transpose the following table without using proc transpose?

gamotte — Thu, 18 Apr 2019 09:50:43 GMT

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;

Re: how to transpose the following table without using proc transpose?

Jonathanzz — Thu, 18 Apr 2019 09:59:38 GMT

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!!!

Re: how to transpose the following table without using proc transpose?

gamotte — Thu, 18 Apr 2019 10:10:35 GMT

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;