This is a program that was left to me and seems to be broke, I'm not sure how it's broke. Essentially there are a few loops that need to run, one for the year, one for the break, and one for the breakdown. I'm not sure if they are in the wrong order or what? option ps=75 ls=68 errors=1 nodate yearcutoff=1940 nocenter MLOGIC MPRINT SYMBOLGEN ;
%include "&path/macros/libname.sas";
%put &path/macros/libname.sas;
%include "&path/macros/tblup.sas";
%put &path/macros/tblup.sas;
/*Works just fine until this point*/
%macro filltableentire_breaks;
proc sql noprint;
/*selecting count of distinct years into the macrovariable*/;
select count(distinct year) into :cntyr from dis_year;
/*to remove the spaces created in the above step*/;
%let cntyr=&cntyr;
/*selecting the distinct year values into range of macro variables created dynamically by resolving the count macro variable*/;
select distinct year into :year1-:year&cntyr from dis_year;
/*iterating for the number of years*/;
/*selecting the count of distinct breakdowns in _breaks_this_indicator */
select count(distinct breakdown) into :cntbreakdown from _breaks_this_indicator;
/*removing the leading spaces*/
%let cntbreakdown=&cntbreakdown;
/**selecting the count of distinct breaks in _breaks_this_indicato*/;
select count(distinct break) into :cntbreak from _breaks_this_indicator;
/*removing leading spaces*/
%let cntbreak=&cntbreak;
/*create breakdown macro variables dynamically using the count macro variable and dash operator*/
select distinct breakdown into :breakdown1-:breakdown&cntbreakdown from _breaks_this_indicator;
/*create break macro variables dynamically using the count macro variable and dash operator*/
select distinct break into :break1-:break&cntbreak from _breaks_this_indicator;
/*creates a nested loop where the outer loop iterates for nunber of break values and the */
/*inner loop iterates for number of breakdown values. A macro variable is created to hold the value of count between the iterations */
%do i=1 %to &cntyr;
%do j=1 %to &cntbreak;
%let m=0;
%let s=0;
%do k=1 %to &cntbreakdown;
/*count is selected into the count macro variable for breakdown and break value.Breakdown
and break values are resolved using forward scan rule of double ampersands. unquote and bquote are used to create value wih single quotes*/
select median(months),months into :med_len from _master
where &&breakdown&k=%unquote(%bquote('&&break&j'))
and year=%unquote(%bquote('&&year&i')) group by year order by months;
select count(*) into :pcount from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
/*removes leading spaces*/;
/*update entire table with s value*/;
%end;
update entire set p=&pcount, med_len=&med_len where break=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
%end;
%end;
quit;
* %do i=1 %to &cntyr;
* %do j=1 %to &cntbreak;
* %let m=0;
* %let s=0;
* %do k=1 %to &cntbreakdown;
* /*count is selected into the count macro variable for breakdown and break value.Breakdown and break values are resolved using forward scan rule of double ampersands. unquote and bquote are used to create value wih single quotes*/
* select median(months),months into :med_len, :lmon from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i')) group by year order by months;
* select count(*) into :pcount from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
* /*removes leading spaces*/;
* %let pcount=&pcount;
* %if &pcount=. %then %let pcount=0;
* %let s=%sysevalf(&pcount+&s);
* /*update entire table with s value*/;
* %end;
* update entire set p=&s, med_len=&med_len where break=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
* %end;
* %end;
*quit;
*end of the macro program;
%mend;
*macro call;
%filltableentire_breaks;
%lib(dmachine);
%tblup(work.entire,dmedlos.entire)
... View more
