Dear KurtBremser, Over te last couple of days I was struggling to fix my code and get result based on your comments. your solution is intresting to me and I am sure this is the best way to do loop over list of disease name and regions. However I have still problem which I coudnt found out the reason over last 2 days. The problem is I couldnt get each loop's output and save into one data set. the below code gave me only one row. Please see my comments in the code. the following is my code after editing. data compres;
set data.datasource20012017filtered;
disease=tranwrd(disease, ",", " ");
disease=tranwrd(disease, "<", " ");
disease=tranwrd(disease, "(", " ");
disease=tranwrd(disease, ")", " ");
disease=tranwrd(disease, ".", " ");
disease=tranwrd(disease, "-", " ");
disease=compress(disease);
disease=substr(disease,1,min(length(disease),16));
run;
proc sql;
create table control as
select distinct "EASTERN" as region, disease from Report8
union select distinct "CENTRAL" as region, disease from Report8
union select distinct "SOUTHWEST" as region, disease from Report8
union select distinct "SOUTHEAST" as region, disease from Report8
union select distinct "NORTHWEST" as region, disease from Report8;
quit;
data compres2;
set control;
disease=tranwrd(disease, ",", " ");
disease=tranwrd(disease, "<", " ");
disease=tranwrd(disease, "(", " ");
disease=tranwrd(disease, ")", " ");
disease=tranwrd(disease, ".", " ");
disease=tranwrd(disease, "-", " ");
disease=compress(disease);
disease=substr(disease,1,min(length(disease),16));
run;
%macro analysis(regi,dise,counter);
%put ®i;
%put &dise;
%put &counter;
proc sql;
create table withoutState as
select week, year, count from compres
where region="®i" and disease="&dise"
union
select * from data.mtx3;
quit;
proc sql;
create table sumup_count as
select distinct week, year , sum(count) as count
from withoutState
group by week, year;
quit;
proc sort data=sumup_count;
by week year;
run;
*using the following proc transpose, we are gonna make the matrix;
proc transpose data=sumup_count out=want (DROP=_LABEL_ AND _NAME_) prefix=year ;
by week;
id year;
var count;
run;
data the_matrix_query_withnull;
retain week year2001 year2002 year2003 year2004 year2005 year2006 year2007 year2008 year2009 year2010 year2011 year2012 year2013 year2014 year2015 year2016 year2017;
set want;
run;
*the additional below data step is because we handel
the null and zero issue in the recomputation limits part.
in fact we need to put zero instead of null on missing value;
data the_matrix_query;
set the_matrix_query_withnull;
if year2001=" " then year2001=0;
if year2002=" " then year2002=0;
if year2003=" " then year2003=0;
if year2004=" " then year2004=0;
if year2005=" " then year2005=0;
if year2006=" " then year2006=0;
if year2007=" " then year2007=0;
if year2008=" " then year2008=0;
if year2009=" " then year2009=0;
if year2010=" " then year2010=0;
if year2011=" " then year2011=0;
if year2012=" " then year2012=0;
if year2013=" " then year2013=0;
if year2014=" " then year2014=0;
if year2015=" " then year2015=0;
if year2016=" " then year2016=0;
if year2017=" " then year2017=0;
run;
data statprocess;
set the_matrix_query;
avg=(sum(of year2007-year2016))/10;
x0=1;
x1=sin(2*3.14159*week/52);
x2=cos(2*3.14159*week/52);
x3=sin(4*3.14159*week/52);
x4=cos(4*3.14159*week/52);
run;
proc reg data=statprocess;
model avg=x0;
output out=trig_®i._&dise. p=yhat r=e stdp=sd;
run;
data _null_;
*set compres2 ; ---> here I removed setting onother data set because it goes through each row for each call execute analysis (there is a call execure outside to run the whole macro multiple times)
%put &counter;
if &counter= 1
then do;
call execute('data all_results; set ');
end;
call execute('trig_'!!strip("®i")!!'_'!!strip("&dise")!!'');
if &counter=73
then do;
%put "samira"; --> here I found out that "samira" repeated 73 times that mean something is wrong because 73 is the amount of rows that I have so it should just execute only one time.
call execute(';run;');
end;
run;
%mend;
data _null_;
set compres2;
_n_=1;
counter=_n_;
call execute('%analysis('!!%str(strip(region))!!','!!%str(strip(disease))!!','!!strip(counter)!!');');
run;
proc print data=all_results(where=(week=3)); --> here I just got one row data. I expected to see all the outputs which come from each loop.
run; I really appriciate all your helps. Samira
... View more
