Context: I am creating an automated report about feelings toward study. In the full version, I have responses from 25 regions with 10-40 cities each, and 100-300 responses from each city (50,000+ students total). The report has 40 modules that are similar to the one I am working on, so automating everything is essential, else I will be hand writing 400+ reports 2 times a year. I use macros for the report because I can isolate the code for each module. Task: I am trying to make the report bullet proof by allowing it to work no matter how many cities there are per region (1 region might have 2 cities, another might have 30, and reports aggregate by region). In the report, I want to list the city frequency next to the region frequency. Version: SAS EG 7.13. It is posted in this forum because I am hard coding everything, not using drag and drop menus. Dataset description: The sample dataset 'SCHOOL.csv' is simplified to have 2 regions with 2-3 cities each, with 5-45 students each. There are two study quality variables studyqual1_1 (Study brings personal fulfillment) studyqual1_2 (I feel study prepares me for a career). region is the administrative region and city is the metro area the respondent lives in. Help needed: I want to SAS to detect how many cities are in each region automatically for the macro. As of now, I would have to manually input the number of cities per region. The current code produces one page with all '.' responses, and scaling this up would produce hundreds of pages of blank reports. Code: /*I set up an index variable to loop in the macros*/
proc sort data=school out= edu ;
by region city;
run;
data regionindex; set edu ;
by region city;
retain regionidx 0;
retain cityidx 0;
if first.region then regionidx=regionidx+1;
if first.region or first.city then do;
if first.region then cityidx=0;
cityidx=cityidx+1;
end;
run;
/*Creating report*/
ods escapechar='~';
ods pdf file="path/testnew.pdf";
options nobyline nodate;
%macro region;
%do j=1 %to 2;
/*I want to change this to be automated, like a dim(var) function in an array*/
%macro city;
%do k=1 %to 2;
/*I want to change this to be automated, like a dim(var) function in an array, but nested within region*/
%macro m22;
%let vars=studyqual1_1|studyqual1_2;
%let names=one|two;
proc format;
value onefive(notsorted)
0='0'
1='1'
2='2'
3='3'
4='4'
5='5'; run;
/* calculate city responses*/
%do I=1 %to 2;
%let vars1=%scan(&vars,&I,|);
%let names1=%scan(&names,&I,|);
/* data output by city*/
proc summary data=regionindex nway completetypes;
class &vars1 /preloadfmt order=data missing;
where regionidx=&j and cityidx=&k;
format &vars1 onefive.;
output out=citykeeprow;
run;
proc freq data=citykeeprow order=data noprint;
tables &vars1 /out=citydatcol&i (keep=percent rename=(Percent=&names1 )) missing nocol nocum nofreq;
weight _freq_/ zeros;
format &vars1 onefive.;
run;
data citydattab (drop=i); merge citydatcol1-citydatcol2;
array new (*) one--two;
do I= 1 to 2;
new(I)=new(I)/100;
end;
run;
/* region output setup*/
proc summary data=regionindex nway completetypes;
class &vars1 /preloadfmt order=data missing;
where regionidx=&j;
format &vars1 onefive.;
output out=keeprowregion;
run;
proc freq data=keeprowregion order=data noprint;
tables &vars1 /out=datcolreg&i (keep=percent rename=(Percent=&names1 )) missing nocol nocum nofreq;
weight _freq_/ zeros;
format &vars1 onefive.;
run;
data regdattab (drop=i); merge datcolreg1-datcolreg2;
array new (*) one--two;
do I= 1 to 2;
new(I)=new(I)/100;
end;
run;
%end;
proc transpose data=citydattab out=citydatatable prefix=citycol;
format _NUMERIC_ Percent8.0;
run;
proc transpose data=regdattab out=regdatatable prefix=regcol;
format _NUMERIC_ Percent8.0;
run;
data labelformats;
length formatlab $ 500;
formatlab='~S={color=#7F7F7F font_face="Arial Light" font_size=14pt}My study brings personal fulfillment~S={}' ; output;
formatlab='~S={color=#7F7F7F font_face="Arial Light" font_size=14pt}My study prepares me for a career~S={}' ; output;
run;
data combtab;
merge labelformats citydatatable regdatatable;
citycol4=citycol4+citycol5;
regcol4=regcol4+regcol5;
run;
proc report data=combtab
style(report)=[frame=void bordercolor=white background=white]
style(header)={font_size=10pt font_face='Arial Light' color=#1F4E79}
style(column)={font_size=14pt font_face='Arial' just=center};
col formatlab citycol4 regcol4;
define formatlab/style(column)={cellwidth=3.75in cellheight=.25in} "~S={ color=#026FA6 font_face='Arial' font_size=16pt just=left}Fulfillment from study";
define citycol4 /right "Almost always or always" style(column)={cellheight=.33in cellwidth=.75in};
define regcol4 /right "Region" style(column)={cellheight=.33in cellwidth=.75in};
run;
%mend m22;
%m22;
%end;
%mend city;
%city;
%end;
%mend region;
%region;
ods pdf close ;
... View more