Transposing (?) Data

[acros]

Hi,

I have a dataset that looks like this:

ID       Type     Provider

01       CBE          A    

01       Mam         A

01       US            B

01       Biopsy       B

I want it to look like this:

ID     CBE     Mam     US     Biopsy

01       A          A         B         B

Any suggestions how to do this?

Amanda

[CTorres · Accepted solution]

data have;

  length ID $ 2 Type $ 8 Provider $ 2;

  input id type provider;

  cards;

01 CBE    A

01 Mam    A

01 US     B

01 Biopsy B

;

run;

proc transpose data=have out=want(drop=_name_);

by ID;

id type;

var provider;

run;

Regards,

[acros]

I'm getting an error because my dataset actually looks like this:

ID       Type     Provider    Date

01       CBE          A            1/2/13

01       Mam         A            1/2/13

01       US            B            1/2/13

01       Biopsy       B            1/2/13

01       Biopsy       B            3/5/13

A Type can occur twice in the same BY group.

Do you have a solution within the transpose procedure? If not, I think I could create a new variable that's a concatenation of the Type and Date, then transpose this new variable.

[art297]

Given your latest example dataset, what do you want the transposed file to look like?

[acros]

ID     CBE     Mam     US     Biopsy1    Biopsy2

01       A          A         B         B               B

[Reeza]

What if the ID02 has 2 Mam? How would you expect it to look then?

[art297]

Fareeza,

Precisely what I was thinking, but I chose two CBEs.  I'm sure the following code could be generated, but here is my quick manual solution:

data have;

  input (id type provider) ($) date mmddyy8.;

  format date date9.;

  cards;

01       CBE          A  1/2/13

01       Mam          A  1/2/13

01       US           B  1/2/13

01       Biopsy       B  1/2/13

01       Biopsy       B  3/5/13

02       CBE          A  1/2/13

02       CBE          A  1/2/13

02       US           B  1/2/13

02       Biopsy       B  3/5/13

;

proc freq data=have;

  tables type/out=types;

  by id;

run;

proc sql;

  create table counts as

    select distinct type, max(count) as cnt

      from types

        having count=max(count) and count gt 1

  ;

quit;

data need;

  set have;

   by id;

  if first.id then do;

    Biopsy_Counter=0;

    CBE_Counter=0;

  end;

  if type eq 'Biopsy' then do;

    Biopsy_Counter+1;

    type=catt(type,Biopsy_Counter);

  end;     

  else if type eq 'CBE' then do;

    CBE_Counter+1;

    type=catt(type,CBE_Counter);

  end;

run;

proc transpose data=need out=want (drop=_:);

  by ID;

  id type;

  var provider;

run;

data want;

  retain id CBE1 CBE2 Mam US Biopsy1 Biopsy2;

  set want;

run;

[acros]

Thank you.

[Astounding]

One approach, for a limited number of categories:

proc transpose data=have out=cbe prefix='CBE';

   by id;

   var provider;

   where type='CBE';

run;

proc transpose data=have out=biopsy prefix='Biopsy';

   by id;

   var provider;

   where type='Biopsy';

run;

proc transpose data=have out=mam prefix='Mam';

   by id;

   var provider;

   where type='Mam';

run;

proc transpose data=have out=us prefix='US';

   by id;

   var provider;

   where type='US';

run;

data want;

   merge cbe biopsy mam us;

   by id;

run;

The spelling of TYPE can be critical ... you might want to check that it is consistent across observations.

Good luck.