Help using Base SAS procedures

Transposing (?) Data

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Transposing (?) Data

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


Accepted Solutions
Solution
‎05-21-2014 01:33 PM
Regular Contributor
Posts: 180

Re: Transposing (?) Data

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,

View solution in original post


All Replies
Solution
‎05-21-2014 01:33 PM
Regular Contributor
Posts: 180

Re: Transposing (?) Data

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,

Contributor
Posts: 24

Re: Transposing (?) Data

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.

PROC Star
Posts: 7,473

Re: Transposing (?) Data

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

Contributor
Posts: 24

Re: Transposing (?) Data

ID     CBE     Mam     US     Biopsy1    Biopsy2

01       A          A         B         B               B

Super User
Posts: 19,792

Re: Transposing (?) Data

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

PROC Star
Posts: 7,473

Re: Transposing (?) Data

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=_Smiley Happy;

  by ID;

  id type;

  var provider;

run;

data want;

  retain id CBE1 CBE2 Mam US Biopsy1 Biopsy2;

  set want;

run;

Contributor
Posts: 24

Re: Transposing (?) Data

Thank you.

Super User
Posts: 5,504

Re: Transposing (?) Data

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 292 views
  • 0 likes
  • 5 in conversation