title; option pageno=1;

libname d "c:\data\preavg";
libname da "c:\data\avg";


*Merge all  the data together. New and old data. So that we will have a complete data set to work with;
data new;
   merge d.d1 d.d2 d.d3 d.d4 d.d5; by mass;
   rename p1-p1053=s1-s1053;
run;

*Transpose the data so that we can average the data;
proc transpose data=new out=trans;
var s1-s1053;
run;

*Change the names of some variables for easier readability. 
This will be useful when verifying the data is correct, and when merging the id info in;
data trans; set trans; 
*id=substr(_NAME_,2); 
rename _NAME_=specname COL1-COL2386=p1-p2386; run;

*Sort the trans_id so that we can merge the data;
proc sort data=d.trans_id out=t_id; by specname; run;
proc sort data=trans; by specname; run; 

*Merge the transpose id data into the transposed data. Then sort by id for to maintain the original order;
data trans; merge t_id trans; by specname;

*Replace the zeros with missing values. 
This is done so that when the average is taken, the zeros will not influence the mean.; 
%macro zero2dot;
%do i=1 %to 2386;
if p&i=0 then p&i=.;
%end;
%mend;
%zero2dot;
run;

proc sort data=trans; by id; run;

*data d.trans; *set trans; *run;

*Sort the trans data by biopsy_num so that the proc means will work;
proc sort data=trans;by biopsy_num;run;


*Get the means into m_trans;
proc means data=trans mean noprint;
var p1-p2386;
by biopsy_num;
output out=m_trans mean=m1-m2386;
run;

*We should be done with trans; 
proc sort data=trans;by id;run;


proc sort data=m_trans; by biopsy_num; run;

*Transform the . back to 0. and write to disk;
data trans_avg;
set m_trans;
drop _freq_ _type_;

%macro dot2zero;
%do i=1 %to 2386;
if m&i=. then m&i=0;
%end;
%mend;
%dot2zero;
run;




*Transpose the log10 data set so that external programs can use them;
proc transpose data=trans_avg out=da.data_avg;
run;