* a mostly final version of the macro!;

*** Program: _report_macro.sas *** Author: BJ Mattson *** Contributing *** Author: Mary Howard for the SQL code generation *** Date: 02/11/2009; options nocenter ps=max ls=150 formdlim='-' fmtsearch=(brfss07); ods listing close; /* colvar = variable of interest maxcoln1 = maximum value of column variable + 1 colfmt = format for variable of interest rowvar = classifications of interest maxrown1 = maximum value of row variable + 1 rowfmt = format for variable of interest */ run; %macro GetCleanRaw(colvar = , maxcoln1 = , colfmt = , rowvar = , maxrown1 = , rowfmt = ) ;

proc surveyfreq data=brfss07.testmisfix nosummary; tables &rowvar * &colvar / cl col row wtfreq; ods output CrossTabs=brfss07.&rowvar&colvar ; weight _finalwt; run; data brfss07.&rowvar&colvar.cleaned ; set brfss07.&rowvar&colvar ; array cleanup (9) Percent LowerCL UpperCL ColPercent ColLowerCL ColUpperCL RowPercent RowLowerCL RowUpperCL ; do i = 1 to 9 ; if missing(cleanup(i))=0 then cleanup (i) = round(cleanup(i),.1) ; end ; drop i ; format frequency WgtFreq comma10.0 RowPercent RowLowerCL RowUpperCL ColPercent ColLowerCL ColUpperCL Percent LowerCL UpperCL 5.1 ; if _n_ > 1 then table=""; * prepare the confidence limits to print on the table; Rowlimits=put(compbl(catt("(",put(RowLowerCL,4.1),"-",put (RowUpperCL,4.1),")")),$12.); if missing(RowPercent) = 1 or RowPercent = 100 then rowlimits=""; Collimits=put(compbl(catt("(",put(ColLowerCL,4.1),"-",put (ColUpperCL,4.1),")")),$12.); if missing(ColPercent) = 1 or ColPercent = 100 then collimits=""; Totlimits=put(compbl(catt("(",put(LowerCL,4.1), "-",put (UpperCL,4.1), ")")),$12.); if missing(Percent) = 1 or Percent = 100 then totlimits=""; * concatenate the percents with the matching confidence limits so they will transpose together a slash is usedfor the delimiter, as a comma is used in the format of WgtFreq; if RowPercent = 100 then RowEntry = catt(RowPercent); else if missing(RowPercent) = 0 then RowEntry = catt (RowPercent,"/ ",compbl(Rowlimits)); if ColPercent = 100 then ColEntry = catt(ColPercent); else if missing(ColPercent) = 0 then ColEntry = catt (ColPercent,"/ ",compbl(Collimits)); if Percent = 100 then TotEntry = catt(Percent); else if missing(Percent) = 0 then TotEntry = catt (Percent, "/ ",compbl(Totlimits)); * This creates a text version of your row and column variables; &rowvar.Level = put(&rowvar,&rowfmt); If &rowvar = . then &rowvar.Level = "Total"; &colvar.Level = put(&colvar,&colfmt); If &colvar = . then &colvar.Level = "Total"; * assign the total a value 1 greater than the variable max - you need it to sort them properly; * assumes the values start at 1 and will increment by 1; if missing(&colvar) = 1 then &colvar = &maxcoln1 ; if missing(&rowvar) = 1 then &rowvar = &maxrown1 ; * put labels on your fields, as they will label the rows after you transpose; label frequency = 'Frequency' RowEntry = 'Row %, Confidence Limits' ColEntry = 'Column %, Confidence Limits' TotEntry = 'Total %, Confidence Limits' &rowvar.Level = "&rowvar Level" &colvar.Level = "&colvar Level"; * no longer using the following, so dump them to save time and storage space; drop StdDev Control RowPercent RowStdErr Rowlimits RowLowerCL RowUpperCL ColPercent ColStdErr Collimits ColLowerCL ColUpperCL Percent StdErr Totlimits LowerCL UpperCL ; run; proc sort data=brfss07.&rowvar&colvar.cleaned ; by &rowvar &colvar; run ; PROC TRANSPOSE data=brfss07.&rowvar&colvar.cleaned out= brfss07.&rowvar&colvar.turned label=TableRowLabel prefix=&colvar ; by &rowvar ; ID &colvar ; var &colvar &colvar.Level &rowvar.Level Frequency WgtFreq RowEntry ColEntry TotEntry; run; ods html file="c:\brfss\results\BRFSS07.&rowvar&colvar.turned.htm"; proc print data=BRFSS07.&rowvar&colvar.turned noobs; run; ods html close; data brfss07.&rowvar&colvar.turnedout; set brfss07.&rowvar&colvar.turned; * delete the unformatted values; if _NAME_ in ("&colvar") then delete ; if _NAME_ in ("&colvar.Level") then do ; TableRowLabel = "&colvar.Level" ; _NAME_ = ""; end; if _NAME_ in ("&rowvar.Level") then TableRowLabel = &colvar.1 ; label TableRowLabel = ; array cleancols (&maxcoln1)$ &colvar.1-&colvar&maxcoln1; length &colvar.CL1-&colvar.CL&maxcoln1 $12. ; array columncls (&maxcoln1)$ &colvar.CL1-&colvar.CL&maxcoln1 ; do i = 1 to &maxcoln1 ; if trimn(_NAME_) in ("Frequency","WgtFreq","RowEntry","ColEntry","TotEntry") then cleancols(i) = compbl(cleancols (i)); if _NAME_ in ("&rowvar.Level") then cleancols (i) = ''; if trimn(_NAME_) in ("RowEntry","ColEntry","TotEntry") then do; if index(cleancols(i),'/') > 0 then do; columncls (i) = substr(cleancols(i) , (index(cleancols (i),'/')+1) ); cleancols (i) = substr(cleancols(i) ,1,(index(cleancols (i),'/')-1) ); end; end; end ; if trimn(_NAME_) in ("Frequency","WgtFreq","RowEntry","ColEntry","TotEntry") then _NAME_ = "" ; if _N_ > 2 and TableRowLabel in ("&colvar.Level") then delete; * drop i ; keep _NAME_ TableRowLabel &colvar.1-&colvar&maxcoln1 &colvar.CL1- &colvar.CL&maxcoln1 ; rename _NAME_=&rowvar.Level ; run; ods html file="c:\brfss\results\BRFSS07.&rowvar&colvar.turnedout.htm";

* the following code written by Mary Howard;

filename temp6 "c:\temp\temp6.txt"; data codeset; informat var1 $600. var1 $600. var2 $600. var3 $600. var4 $600. var5 $600. var6 $600.ichar $6.; file temp6; var1= ' proc sql noprint; '; var2= ' create table brfss07.&rowvar&colvar.turnedout2 as '; var3= ' select &rowvar.Level, TableRowLabel, '; if _n_=1 then do; put var1; put var2; put var3; end; do i=1 to &maxcoln1; ichar=compress(put(i,6.),' '); if i < &maxcoln1 then do; var4= " &colvar" || trim(ichar) || ',' || "&colvar.CL" || trim (ichar) || ', '; put var4; end; else do; var4= " &colvar" || trim(ichar) || ',' || "&colvar.CL" || trim (ichar); var5= ' from brfss07.&rowvar&colvar.turnedout; '; var6= ' quit '; put var4; put var5; put var6; end; end; run;

%inc temp6/source2;

ods html file="c:\brfss\results\BRFSS07.&rowvar&colvar.turnedout.htm"; proc print data=BRFSS07.&rowvar&colvar.turnedout2 noobs; run; ods html close; PROC EXPORT DATA= BRFSS07.&rowvar&colvar.turnedout2

OUTFILE= "c:\brfss\results\&rowvar&colvar.turnedouttabdelim.txt" DBMS=TAB REPLACE; RUN; %mend; * testing on BRFSS 2007 data; %GetCleanRaw (colvar = GENHLTH, maxcoln1 = 6 , colfmt = genhlth. , rowvar = _BMI4CAT, maxrown1 = 4 , rowfmt = _bmi4cat.) ;