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

J_Park — Sat, 19 Apr 2025 21:59:30 GMT

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;

Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va

PaigeMiller — Sun, 20 Apr 2025 09:43:56 GMT

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.

Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va

ballardw — Sun, 20 Apr 2025 20:09:36 GMT

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.

Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va

RichardAD — Mon, 21 Apr 2025 11:18:45 GMT

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.

topic Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va in SAS Programming

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

Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va

Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va

Re: Table Macro revision to take out Missing category from calculating percentage for Categorical Va