topic Re: Transposing (?) Data in SAS Procedures

Transposing (?) Data

acros — Wed, 21 May 2014 17:21:47 GMT

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

Re: Transposing (?) Data

CTorres — Wed, 21 May 2014 17:33:50 GMT

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,

Re: Transposing (?) Data

acros — Wed, 21 May 2014 17:55:08 GMT

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.

Re: Transposing (?) Data

art297 — Wed, 21 May 2014 18:04:28 GMT

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

Re: Transposing (?) Data

acros — Wed, 21 May 2014 18:07:38 GMT

ID CBE Mam US Biopsy1 Biopsy2

01 A A B B B

Re: Transposing (?) Data

Reeza — Wed, 21 May 2014 19:20:52 GMT

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

Re: Transposing (?) Data

art297 — Wed, 21 May 2014 19:38:47 GMT

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

Re: Transposing (?) Data

Astounding — Wed, 21 May 2014 19:46:39 GMT

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.

Re: Transposing (?) Data

acros — Wed, 21 May 2014 21:08:25 GMT

Thank you.