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