Table Macro revision to take out Missing category from calculating percentage for Categorical Var

[J_Park]

I am new using Macro to create Tables and using Macro code that someone else created.   It includes Missing data as a separate category, so the percentages are not correct.  The code is listed below.   Would someone look at the code and see how I can revise it not to include the missing in calculating the percentage please?    I am attaching the example Table 1.  Thank you for your help in advance!

              proc sql;

               select count(&id) into :n  from new;

               select count(distinct(&trt)) into :trtn  from new;

              quit;

              %macro fisher_or_chi(data=, row=, col= );

                             proc freq data=&data noprint;

                               tables &row*&col / sparse outexpect out=_out1 (where=(not missing(&col)));

                               run;

                             proc means data=_out1 noprint;

                               var count;

                               output out=_out2;

                               run;

                             proc sql;

               select count(DISTINCT(&row)) into :rown  from &data;

               select count(DISTINCT(&col)) into :coln  from &data;

               select sum(&col) into :colcount  from &data;

                  quit;

                             data _null_;

                               set _out1;

                               if expected<=5 then warn+1;

                               if _n_=1 then set _out2;

                               pct_lt5=warn/_freq_;

                               if _freq_=_n_;

                               warning=(pct_lt5>=.2);

                               call symput('warning',warning);

                               run;

                             quit; 

                             %put &rown;

                             %put &coln;

                             %put &colcount;

                              %if &warning=1 and &rown<=2 and &coln<=2 and &coln ne 1 %then %do;

                                   proc freq data=&data;

                                   tables &row*&col/   fisher out=FreqCount;

                                             output out= pvalue (rename=(XP2_FISH=pvalue) keep=XP2_FISH) fisher;

                                   run;

                                             data PVALUE_Exists;

                                                         set PVALUE  ;

                                             run;

                              %end;

                              %else /*%if &warning ne 1 %then*/ %if &colcount ne 0 %then %do;

                                   proc freq data=&data;

                                   tables &row*&col /   chisq out=FreqCount;

                                             output out= pvalue  (rename=(P_PCHI=pvalue)) chisq;

                                   run;

                                             data PVALUE_Exists;

                                             format pvalue pvalue6.4;

                                             set pvalue   ;

                                             run;

                              %end;

                              %else %do;

                                               proc freq data=&data;

                                   tables &row*&col /   chisq out=FreqCount;

                                   run;

                                             data PVALUE_Exists;

                                             format pvalue pvalue6.4;

                                              pvalue=.;

                                             run;

                              %end;

     %mend;

    %fisher_or_chi(data=new, row=&trt, col=&varname);

              proc sort data = FreqCount; by &varname &trt; run;

              proc transpose data = FreqCount out= fc ;

               by  &varname &trt;

               var COUNT ;

              run;

              proc transpose data = fc out= fc1 prefix= count;

                by &varname;

                id &trt;

                var COL1;

              run;

              proc sort data = fc1;      by &varname; run;

              data a;

               set  fc1 ;

               length col0 col1 $200 ;

                col0 = "&varname";

                sort = &sort;

                if &varname = ' ' then col1 = 'Missing';

                             else col1 = &varname;

              run;

                             proc sql;

                                            %do i=1 %to &trtn;

                                           select sum(count&i) into :c&i from fc1 ;

                                            %end;

                             quit;

              proc sort data =  a; by sort; run;

              data &varname;

               length col1 $200;

                merge  PVALUE_Exists  a (where=(&varname=1));

                              COL_ = &label;

                              type = "&type";

                              trtn = &trtn.;

                                 n=&n.;

                                 sort0 + 1;

                              %do i=1 %to &trtn;

                               c&i = &&c&i;

                               if count&i = . then count&i = 0;

                               if c&i = . then c&i = 0;

                    if count&i ne 0 then COL_&i = compress(put(count&i,8.))||' ('||compress(put(round((count&i/c&i)*100,0.1),8.1))  ||'%'||')';

                               else if count&i = 0 then COL_&i = compress(put(count&i,8.))||' ('||compress(put(round((count&i)*100,0.1),8.1))  ||'%'||')';

                               countt= sum(of count1-count&i);

                   %end;

                              COL_T = compress(put(countt,8.))||' ('||compress(put(round((countt/n)*100,0.1),8.1))  ||'%'||')';

                                            if .z<pvalue<0.05 then colp = put(pvalue,pvalue6.4)||'*';

                             else if pvalue ne . then colp = put(pvalue,pvalue6.4);

                             else colp = ' ';

                             *** delete pvalue exists indicator row ***;

                             if DSObs > 0 then delete;

                  %do i=1 %to &trtn;

                               if count&i = . then count&i = 0;

                             %end;

              run;

[PaigeMiller]

Most of us (including me) will not download attachments of Microsoft Office documents as they are security threats. And furthermore, I will not download any document attachments.

Whatever you want us to see in the MS Word document, paste it into your message as text (or if it is a graphic, paste it into your message as a graphic).

Also, please run this command

options mprint;

and then run your code again, and see if you can figure out what is wrong by checking the log. If you're still stuck, show us the ENTIRE log (down to and including the first ERROR message) for this macro by clicking on the </> icon and pasting the log as text into the window that appears. DO NOT SKIP THIS STEP.

[ballardw]

What is that attachment supposed to show us? All I see is a table. There is no way to tell what the problem might be.

We do not have your data set. So assuming that the shown code is supposed to generate that table we can't tell. If the attachment shows an incorrect table it is also up to you show what a correct table would look like. Otherwise we have nothing to work with.

Can you show a completely working example of this process that does not involve any macro code? If you do not have a working example without macro code it is seldom possible to create macro code that works.

[RichardAD]

Change the section where c&i is computed for the denominator

proc sql;
  %do i=1 %to &trtn;
    select sum(count&i) into :c&i from fc1
WHERE COL1 ne 'Missing'
;
%end;
quit;

You will need to apply a similar where clause in your final data step.