topic transposing variables in SAS Programming

transposing variables

SASPhile — Wed, 01 Jul 2009 19:38:58 GMT

Phy_Name Total Drugname
Bruce FREEDMAN 240.6 Paracetemol
Bruce FREEDMAN 15085 Bendex
Bruce FREEDMAN 4190 natropin
Bruce FREEDMAN 1086.6 Saisen
Bruce RICHTON 924.6 Paracetemol
Bruce RICHTON 672 Humasona
Bruce RICHTON 270 Bendex
Bruce RICHTON 170 natropin
Bruce RICHTON 1144 Saisen

For the above data, I want to transpose the drugname and total so that each physician will have a single record.

I tried the following code:
The total is being mismatched with the drugnames.i.e it is getting displaced by one position to the left if the correspoding drug name is missing.
for instance: Phyname Bruce FREEDMAN doesnt have humasona, it should give us a missing instead.but the value of saisen is given a missing and its value is displced by one position to the left.

data new(keep=Phy_Name Paracetemol Saisen natropin Bendex Humasona) ;
set allphysicians;
by Phy_Name ;
array drg {&n} Paracetemol Saisen natropin Bendex Humasona ;
retain Paracetemol Saisen natropin Bendex Humasona;
if first.Phy_Name then i=1;
else i + 1;
drg{i} = var1_tot;
if last.Phy_Name;
if i < &n then do i=i+1 to &n;
drg{i} =.;
end;
run;

Re: transposing variables

sbb — Wed, 01 Jul 2009 19:44:45 GMT

Did you look at PROC TRANSPOSE?

Scott Barry
SBBWorks, Inc.

Re: transposing variables

SASPhile — Wed, 01 Jul 2009 19:46:56 GMT

I tried proc tranpose, but there were issues with duplicate id values.

Re: transposing variables

sbb — Wed, 01 Jul 2009 19:49:25 GMT

And your DATA step (very manual intense) approach can address them? Maybe if you explained the problem, there may be a method to still use PROC TRANSPOSE??

Scott Barry
SBBWorks, Inc.

Re: transposing variables

deleted_user — Wed, 01 Jul 2009 19:54:41 GMT

Hello SASPhile,

Scott's proposal is the correct one. Perhaeps you should aggregate your data before using the proc transpose. The program could be:

data T01_data;
infile cards delimiter='|';
length Phy_Name $30 Drugname $30;
input Phy_Name Total Drugname;
cards;
Bruce FREEDMAN|240.6|Paracetemol
Bruce FREEDMAN|15085|Bendex
Bruce FREEDMAN|4190|natropin
Bruce FREEDMAN|1086.6|Saisen
Bruce RICHTON|924.6|Paracetemol
Bruce RICHTON|672|Humasona
Bruce RICHTON|270|Bendex
Bruce RICHTON|170|natropin
Bruce RICHTON|1144|Saisen
;
run;

proc means data=T01_data NWAY NOPRINT MISSING ;
class phy_name drugname;
var total;
output out=T02_aggregated sum(total)=sum_of_total;
run;

proc transpose data=T02_aggregated out=T03_transposed(drop=_NAME_);
by phy_name;
id drugname;
var sum_of_total;
run;

Does it solve the problem?

Yoba

Re: transposing variables

Peter_C — Thu, 02 Jul 2009 14:23:22 GMT

I think the data step fails because it assumes it will find all drugnames in your array, but the first physician does not deal with Humasona.
Transpose should do[pre]proc sort data= ph out= phy ;
by phy_name drugname ;
run ;
proc transpose data= phy out =phyt ;
by Phy_Name;
id drugname ;
var total ;
run ;[/pre]
As should a data step.
When I had to make a data step do this kind of transpose, I found it helpful to create a user informat to translate the drugName to an array entry pointer. To reduce the code for your simple data, below, I place a drugName every 12th position in a string and calculate the array index entry with a formula based on the indexW() function applied to the &DrugList.[pre]%let drugList =
Paracetemol Humasona Bendex natropin Saisen ;
data phyT_ds ;
keep phy_name &drugList ;
do until( last.phy_name ) ;
set phy;
by phy_name ;
array dr(*) &drugList ;
idx = indexw( "&drugList", trim( drugname ) ) / 12 +1 ;
dr( idx ) = sum( dr( idx ), total ) ;
end ;
run ;
proc print ;
run ;[/pre]
works for me 😉

PeterC