Skip to content

Instantly share code, notes, and snippets.

@statgeek

statgeek/summary_table.sas

Last active October 2, 2018 14:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save statgeek/0ff46bbd76bcc101e40945816b232303 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save statgeek/0ff46bbd76bcc101e40945816b232303 to your computer and use it in GitHub Desktop.
Download ZIP
SAS Summary table chi square
Raw
summary_table.sas
/*--------------------------------------------------------------------------------*/
/* Author: F. Khurshed */
/* Date: June 17, 2011 */
/* Purpose: This macro creates a set of chisquare tables appended together. */
/* It includes the chisquare value. */
/* Modified by: F. Khurshed */
/* Date: January 17, 2012 */
/* Purpose: Adding in the ability for the macro to add the total column. */
/* Modified by: F. Khurshed */
/* Date: February 6, 2012 */
/* Purpose: Add in row of overall by cross category. */
/* Modified by: W. Qiu */
/* Date: December 14, 2016 */
/* Purpose: 1. Make lengths of some output variables consistent to avoid */
/* warning messages; */
/* 2. Re-order output variables for better display; */
/* 3. Remove some unnecessary coding; */
/* 4. Change positional parameters in the macro to keyword parameters */
/* so that the order of parameters doesn't matter; */
/* 5. Add the parameter validation process so that if any parameter is */
/* not correctly listed, a warning message will pop up and execution */
/* stops; */
/* 6. Add a valid value for rc to allow both row and column percentages; */
/* 7. Allow rc to take versatile values as long as the first letter is */
/* r, c, or b (case insensitive); */
/* 8. Add two more parameters: replace and nlevel_cutoff; */
/* 9. Allow cross variable to have missing values and output a seperate */
/* column as the missing category if parameter missing is not null. */
/*--------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------*/
/* PARAMETERS: */
/* dsetin = name of the input dataset */
/* dsetout = name of output dataset */
/* varlist = varlist to perform the operations, seperated by space */
/* varcross = var to be across the top row */
/* rc = row, column or both, depending on what you want the percentages to be. */
/* You don't have to spell completely, as long as the first letter is */
/* r, c, or b, case insensitive */
/* missing = null or missprint or missing, depending on whether you want missing */
/* values to be included in the frequency table or counted in the */
/* percentage */
/* replace = whether allow output dataset to be replaced by a new run. */
/* If not specified as a word with initial of Y or y, no replacement */
/* will be assumed, and if a same named dataset as dsetout already */
/* exists, a warning message will pop up */
/* nlevel_cutoff = a threshold of number of distinct values in a variable, if */
/* larger than it, will consider the variable to have too many */
/* levels and a warning message will pop up. It can take any */
/* integer values */
/*--------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------*/
/* An example of calling the macro: */
/* %summary_table(dsetin=sashelp.class, dsetout=test, varlist=age weight, */
/* varcross=sex, rc=c, missing=missing, replace=y, nlevel_cutoff=20)*/
/*--------------------------------------------------------------------------------*/
%macro summary_table(dsetin=, dsetout=, varlist=, varcross=, rc=, missing=, replace=, nlevel_cutoff=);
/****************************************************/
/************ Parameter Validation Starts ***********/
/****************************************************/
/*check if the cross variable or variables in the varlist exists*/
/*also check if any variable name in input dataset conflicts with default var names in certain procedures*/
%let stop=N;
%let allvar=%sysfunc(catx(%str( ),&varcross.,&varlist.));
%let varexcl=%str(count percent vart value rc category var overall chisq pct_row pct_col);
%if %sysfunc(exist(&dsetin.)) %then %do;
%let i=1;
%do %while (%scan(&allvar, &i, " ") ^=%str());
%let varr=%scan(&allvar, &i);
data _null_;
dsid=open("&dsetin.");
num1=varnum(dsid,"&varr.");
if (num1=0) then do;
file print;
put #3 @10 "The variable &varr. not found in the data set &dsetin.!";
call symput("stop","Y");
end;
run;
%let j=1;
%do %while (%scan(&varexcl, &j, " ") ^=%str());
%let varn=%scan(&varexcl, &j, " ");
%if %upcase(&varr)=%upcase(&varn) %then %do;
data _null_;
file print;
put #3 @10 "The variable name &varn. conflicts with the macro procedure, please rename!";
call symput("stop","Y");
run;
%end;
%let j=%eval(&j+1);
%end;
%let i=%eval(&i+1);
%end;
%if &stop.=Y %then %goto quit;
%end;
/*check if the input dataset exists*/
%else %do;
data _null_;
file print;
put #3 @10 "The input data set &dsetin. does not exist. Please correct!";
run;
%goto quit;
%end;
/*check if output dataset already exists if replacement option not allowed*/
%if %substr(%upcase(&replace.),1,1)~=Y %then %do;
%if (%sysfunc(exist(&dsetout.))) %then %do;
data _null_;
file print;
put #3 @10 "The output data set &dsetout. already exists. Please change the name or allow replacement!";
run;
%goto quit;
%end;
%end;
/*check levels for the variables*/
ods select none;
ods output nlevels=n_levels;
proc freq data=&dsetin nlevels;
table &varcross &varlist;
run;
%let nvar=%eval(%sysfunc(countw(&varlist))+1);
proc sql noprint;
select nlevels, TableVar
into :lvl1-:lvl&nvar, :vlist1-:vlist&nvar
from n_levels;
quit;
ods select all;
/*check if the cross variable only has one level*/
%do i=1 %to &nvar;
%if &lvl1=1 %then %do;
data _null_;
file print;
put #3 @10 "The cross variable &varcross. has only one level!";
run;
%goto quit;
%end;
/*check if any of the variables has too many levels, determined by nlevel_cutoff*/
%if &&lvl&i>&nlevel_cutoff %then %do;
data _null_;
file print;
put #3 @10 "The variable &&vlist&i. has too many levels!";
run;
%goto quit;
%end;
%end;
/****************************************************/
/************ Parameter Validation Ends *************/
/****************************************************/
/*Add in overall row at the top and the totals*/
ods select none;
proc freq data=&dsetin;
table &varcross / chisq &missing out=temp1;
ods output OneWayChiSq=chisq;
run;
/*determine the type of the cross variable first in order to do next steps*/
proc contents data=&dsetin noprint out=vtype; run;
proc sql noprint;
select type into :typecross
from vtype
where upcase(name)=upcase("&varcross");
quit;
%if &typecross=1 %then %do;
data temp2;
length value $ 50;
set temp1 end=eof;
value=catt(left(count)," (",left(put(percent, 8.1)),"%)"); *left function for count is unnecessary;
var='Overall';
%if %length(&missing)~=0 %then %do;
if missing(&varcross) then &varcross=9999;
%end;
keep var &varcross count value;
run;
%end;
%else %do;
data temp2;
length value &varcross $ 50;
set temp1 end=eof;
value=catt(left(count)," (",left(put(percent, 8.1)),"%)"); *left function for count is unnecessary;
var='Overall';
%if %length(&missing)~=0 %then %do;
if missing(&varcross) then &varcross="missing";
%end;
keep var &varcross count value;
run;
%end;
/*Transpose data to better structure*/
proc transpose data=temp2 out=temp3 (drop = _name_ ) prefix=&varcross._;
var value;
id &varcross;
run;
/*Get total count*/
proc sql noprint;
select count(*) into :tot_count
from &dsetin;
quit;
/*Get P-Value from chisq table*/
proc sql noprint;
select nvalue1 into :p_value
from chisq
where name1='P_PCHI';
quit;
data &dsetout;
set temp3;
length vart $256. category $256. overall $256.; /*change length of all string variables to 256 to be consistent and safe*/
vart='Overall';
category='N(%)';
overall=left("&tot_count."||" (100.0%)");
chisq=&p_value;
run;
/*loop through variable list*/
%let i=1;
%do %while (%scan(&varlist, &i, " ") ^=%str());
%let var=%scan(&varlist, &i, " ");
data _null_;
set &dsetin;
call symput("var_fmt", vformat(&var));
run;
/*Get frequency counts and chi-square stats*/
proc freq data=&dsetin;
where not missing(&var.);
tables &var.*&varcross./out=temp1 chisq outpct &missing;
ods output chisq=chisq;
run;
/*get fisher's exact stats if a cell count is less than 5*/
/* proc sql noprint;*/
/* select min(count) into :min_count*/
/* from temp1;*/
/* quit;*/
/**/
/* proc freq data=&dsetin;*/
/* tables &var.*&varcross/exact &missing;*/
/* ods output FishersExact=exact;*/
/* run;*/
/*recode missing and concat into one variable*/
%if &typecross=1 %then %do;
data tmp1;
length value $ 50;
set temp1 (where=( not missing(&var.)));
value=catt(left(count)," (",left(put(pct_row, 8.1)),"%)");
%if %length(&missing)~=0 %then %do;
if missing(&varcross) then &varcross=9999;
%end;
keep &var &varcross count value;
run;
data tmp2;
length value $ 50;
set temp1;
value=catt(left(count)," (",left(put(pct_col, 8.1)),"%)");
%if %length(&missing)~=0 %then %do;
if missing(&varcross) then &varcross=9999;
%end;
keep &var &varcross count value;
run;
%end;
%else %do;
data tmp1;
length value &varcross $ 50;
set temp1;
value=catt(left(count)," (",left(put(pct_row, 8.1)),"%)");
%if %length(&missing)~=0 %then %do;
if missing(&varcross) then &varcross="missing";
%end;
keep &var &varcross count value;
run;
data tmp2;
length value &varcross $ 50;
set temp1;
value=catt(left(count)," (",left(put(pct_col, 8.1)),"%)");
%if %length(&missing)~=0 %then %do;
if missing(&varcross) then &varcross="missing";
%end;
keep &var &varcross count value;
run;
%end;
proc sort data=tmp1;
by &var &varcross;
run;
proc sort data=tmp2;
by &var &varcross;
run;
/*Transpose data to better structure*/
proc transpose data=tmp1 out=tmp3 (drop = _name_ ) prefix=&varcross._;
by &var.;
var value;
id &varcross;
run;
proc transpose data=tmp2 out=tmp4 (drop = _name_ ) prefix=&varcross._;
by &var.;
var value;
id &varcross;
run;
%if %substr(%upcase(&rc.),1,1)=R %then %do;
data temp3;
set tmp3;
rc="Row";
run;
%end;
%else %if %substr(%upcase(&rc.),1,1)=C %then %do;
data temp3;
set tmp4;
rc="Col";
run;
%end;
%else %do;
data tmp5;
set tmp3;
rc="Row";
run;
data tmp6;
set tmp4;
rc="Col";
run;
data temp3;
set tmp5 tmp6;
run;
proc sort data=temp3;
by &var;
run;
%end;
proc datasets nodetails nolist;
delete tmp1-tmp6;
quit;
/*Get the overall counts for each dataset as well*/
proc freq data=&dsetin;
where not missing(&var.);
tables &var/out=overall1 &missing;
run;
data overall2;
length overall $256.;
set overall1;
overall=catt(left(count)," (",left(put(percent, 8.1)),"%)");
keep &var overall;
run;
/*Merge in the overall column*/
data temp3a;
merge temp3 overall2;
by &var.;
run;
/*Add in the test stats*/
data temp4;
length vart $256. category $256.;
set temp3a;
if _n_=1 then do;
set chisq (where=(Statistic="Chi-Square") keep=prob statistic rename=(prob=chisq));
/*set exact (where=(name1="XP2_FISH") keep=Name1 cValue1 rename=(cValue1=fisher));*/
end;
vart=vlabel(&var.);
category=left(put(&var., &var_fmt));
drop &var. statistic name1;
run;
/*
data temp4;
length vart $256. category $256.;
set temp3a;
if _n_=1 then do;
set chisq (where=(Statistic="Chi-Square") keep=prob statistic rename=(prob=chisq));
end;
vart=vlabel(&var.);
category=left(put(&var., &var_fmt));
drop &var. statistic;
run;
*/
/*Append datasets*/
data &dsetout;
set &dsetout temp4;
run;
/*drop temp tables so theres no accidents*/
proc datasets nodetails nolist;
delete temp1-temp4 temp3a chisq /*exact*/;
quit;
/*Increment counter*/
%let i=%eval(&i+1);
%end;
data &dsetout;
retain vart rc category overall;
set &dsetout;
length p_value $8. test $15.;
by vart notsorted;
if first.vart then do;
test='Chi-Square';
p_value=left(put(chisq, 8.4));
seq=1;
end;
else seq+1;
/* if seq=2 then do;*/
/* test="Fisher's Exact";*/
/* p_value=left(put(fisher, 8.4));*/
/* end;*/
drop chisq fisher seq;
run;
/*drop unnecessary tables*/
proc datasets nodetails nolist;
delete overall1 overall2 n_levels vtype;
quit;
ods select all;
%quit:
%mend summary_table;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment