Date:         Thu, 22 Jan 2009 15:55:35 -0500
Reply-To:     BJ Mattson <bj.mattson@ODH.OHIO.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         BJ Mattson <bj.mattson@ODH.OHIO.GOV>
Subject:      Re: manipulating Proc SurveyFreq ODS table output

Here's what I've got so far.  I still need to figure out how to get the
maximum values for the row & column variables, then add 1 to represent the
totals, plus programmatically enter the table name at the top, and any
desired footers. Once I get it working with named variables, then I move on
to macro variables so it can run dynamically.

Your suggestions and advice are appreciated.

BJ Mattson
Epidemiology Investigator III
Ohio Department of Health

proc surveyfreq  data=brfss07.testmisfix nosummary;
   tables _BMI4cat * GENHLTH / cl col row wtfreq ;
   ods output CrossTabs=brfss07.BMI4GENHLTH ;
   weight _finalwt;
   title1  "BRFSS 2007: GENHLTH and obesity";
run;
data brfss07.cleanedBMI4GENHLTH ;
   set brfss07.bmi4GENHLTH ;
   array cleanup (12) Percent    StdErr    LowerCL    UpperCL
                      ColPercent ColStdErr ColLowerCL ColUpperCL
                      RowPercent RowStdErr RowLowerCL RowUpperCL;
   do i = 1 to 12;
      if missing(cleanup(i))=0 then cleanup (i) = round(cleanup(i),.1);
   end;
   drop i;
   format frequency  WgtFreq                         comma9.0
   Percent    StdErr    LowerCL    UpperCL
          ColPercent ColStdErr ColLowerCL ColUpperCL
          RowPercent RowStdErr RowLowerCL RowUpperCL 4.1;
* assign the max value of the var +1 to replace missing value in totals;
   if missing(GENHLTH) = 1 then GENHLTH  = 6;
* need to programmatically assign max of Genhlth + 1 ;
   if missing(_BMI4cat)= 1 then _BMI4cat = 4;
* need to programmatically assign max of _BMI4cat + 1 run;
* Do I need the sort ?;
proc sort data=brfss07.cleanedBMI4genhlth ;
   by _BMI4cat  genhlth ;
run;
ods listing close;
options nocenter ps=max ls=160 formdlim='-' fmtsearch=(brfss07);
ods html;
data brfss07.cleanedBMI4genhlthtest;
   set brfss07.cleanedBMI4genhlth ;
   by _BMI4cat  genhlth ;
   file print PS=58 N=PS NOTITLES LINE=L LINESLEFT=F ;
   col = (Genhlth  *  22) - 10 ; * shift it a bit left;
   L   = (_BMI4CAT * 7);
   if missing(RowPercent) = 0
   then rowlimits=catt("(",RowLowerCL,"-",RowUpperCL,")");
   if missing(ColPercent) = 0
   then collimits=catt("(",ColLowerCL,"-",ColUpperCL,")");
   if missing(Percent)    = 0
   then totlimits=catt("(",LowerCL,"-",UpperCL,")");
   if rowlimits in ("(_-_)","_") then rowlimits="";
   if collimits in ("(_-_)","_") then collimits="";
   if totlimits in ("(_-_)","_") then totlimits="";
   BMILevel =put(_BMI4CAT,_bmi4cat.);
   If _BMI4CAT = 4 then BMIlevel="Total";
   if _n_< 6 then put #L  @col+20 genhlth genhlth.;
   if _n_=6 then put #L  @col+18 "Total";
*  If I can use a formatted value for the total, it'd be better;
   put #L+1 @1 BMIlevel  /
 @11 "Frequency"  @col+18 Frequency  comma9.0               /
 @11 "Weighted N" @col+18 WgtFreq   comma9.0                /
        @11 "Row %"      @col+22 RowPercent      5.1  +1 RowLimits /
        @11 "Column %"   @col+22 ColPercent      5.1  +1 ColLimits /
        @11 "Total %"    @col+22 Percent         5.1  +1 TotLimits ;
* need to add footer lines for citation, references, etc.;
run;
ods html close;