BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
UcheOkoro
Lapis Lazuli | Level 10

Hello ,

 

Please, I need help with a SAS macro command that would give me row percent for descriptive tables. I am presently getting column percent which is not what I want. Below is my sas macro.

 

Thank you .

 

%TABLE1NDONE(
DATASET = stacked22, /* Input data set */
DRIVER = R:\Research\ Analysis Folder\Table1nDoneDrive.xlsx,
OUTPATH= R:\Research\ Analysis Folder\ , /* File pathway for RTF output */
FNAME= Table 1 SS Demographics by Confidence, /* File name for RTF output */
OVERALL = Y, /* Include overall summary statistics */
BYCLASS = Y, /* Include summary statistics by group */
CLASSVAR = confident_rc1, /* Class variable */
DISPLAY_PVAL = Y, /* Display p-values in RTF report */
DISPLAY_METRIC = N, /* Display metric names in RTF report */
DISPLAY_N = Y /* Display non-missing counts in RTF report */
);

 

1 ACCEPTED SOLUTION

Accepted Solutions
UcheOkoro
Lapis Lazuli | Level 10

I found the portion of code that computes the percentage and made the change from column to row and it worked. The specific portion is highlighted in red.

Thank you all for the help.

 

		data Table1.freq2_&varcat_s. (rename = (&var. = Category));
					retain Variable;
					set Table1.freq_&varcat_s.;
					/* combine count and percent into single variable */
					NPercent = cat(count, " (",strip(put(PCT_ROW,7.&deccnt.)), "%)");
					Variable = "&var.";
				run;

				proc transpose data = Table1.freq2_&varcat_s. out = Table1.freqt_&varcat_s. (drop = _NAME_);
					by Variable Category;
					var NPercent;
					id StandClass;
				run;

View solution in original post

10 REPLIES 10
sbxkoenk
SAS Super FREQ

Hello,

 

Where is that macro coming from?

I do not know the macro "%TABLE1NDONE". I do not think it is a SAS-supplied macro.

It is a custom macro written by a user (within your company?)!

 

Hence, without macro definition we can not do anything for you.

 

Cheers,

Koen

UcheOkoro
Lapis Lazuli | Level 10

Here is where the macro came from.

 

Title: Table1nDone Macro
Date: 2019/10/02
Author: Martha Wetzel
Purpose: Run basic statistics by group
	Currently, categorical variables must be character type. 
	Macro parameters:
	Required:
		DATASET=,  Input data set 
		DRIVER=, File path and name for Excel driver file - no quotes
		OUTPATH=, File pathway for RTF output, no quotes 
		FNAME=,  File name for RTF output 
		CLASSVAR=, CAN ONLY TAKE ONE CLASS VARIABLE 
	Optional:
		OVERALL=Y, Request overall summary statistics 
		BYCLASS=Y, Request statistic by class variable 
		OUTLIB=WORK ,  Name of library to save out SAS data sets to 
		ODSGRAPHS=N,  Create or suppress ODS graphics (Y/N)
		ROUNDTO=0.01 Decimal place to round results to  
		DISPLAY_PVAL=Y,  Display p-values in RTF report (Y/N)
		DISPLAY_METRIC=Y,  Display metric names in RTF report (Y/N)
		DISPLAY_N =Y  Display variable N's in RTF report (Y/N)
		CLEARTEMP = Y, Delete all the temp files created by the macro. 
		EXCLUDEMISS=Y, Exclude observations with a missing BYCLASS value from the overall calculations. Only use if both 
			OVERALL = Y and BYCLASS = Y. 
	Input:
		Excel driver: The macro is operated using a Excel driver that includes a list of variable names,
		labels, types, and requested test. 
		In data: The macro requires an input SAS data set with categorical variables in character format.
	Output:  
		The macro outputs two SAS data sets and one RTF table. The SAS data sets 
		contain a variety of statistics of interest to the analyst, including 
		N's and results of alternative statistical tests and (for continuous variables) means, 
		medians, percentiles, min, max, mode, normality test statistics, and counts of missing values. The RTF output contains 
		a limited number of columns that can easily be used in a demographic table for a paper.
-----------------------------------------------------------*/

%MACRO Table1nDone(
	DATASET=, /* Input data set */
	DRIVER=, /* File path and name for Excel driver file - no quotes*/ 
	OVERALL=Y, /* Request overall summary statistics */
	BYCLASS=Y, /* Request statistic by class variable */
	EXCLUDEMISS=Y, /* Exclude observations with a missing BYCLASS value from the overall calculations */
	OUTLIB= WORK, /* Name of library to save out SAS data sets to */
	OUTPATH=, /* File pathway for RTF output, no quotes */
	FNAME=,  /* File name for RTF output */
	CLASSVAR=confident_rc1, /* CAN ONLY TAKE ONE CLASS VARIABLE */
	ODSGRAPHS=N, /* Create or suppress ODS graphics (Y/N) */
	ROUNDTO=0.01, /* Decimal place to round results to */ 
	DISPLAY_PVAL=Y, /* Display p-values in RTF report */
	DISPLAY_METRIC=Y, /* Display metric names in RTF report */
	DISPLAY_N =Y, /* Display variable N's in RTF report */
	CLEARTEMP = Y /* Delete temp files created by macro */
);  

	option label;

	/* Establish a temp directory for macro files */
	%let work_path=%sysfunc(pathname(work));
	options dlcreatedir;
	libname Table1 "&work_path.\Table1";

	/* Get rid of any existing files Table1 directory */
	proc datasets lib=Table1 nolist kill;
	run;
	quit;

	/* Set ODS graphics option */
	%if %sysfunc(upcase(&ODSGRAPHS.)) = Y %then %do;
		ods HTML;
		ods graphics;
		%put NOTE: ODS graphics are on;
	%end;
	
	/* Requested decimal format */
	%let deccnt = %length(%scan("&ROUNDTO.", 2, "."));

	/* Make sure driver file exists */
	%if %sysfunc(fileexist(&Driver.)) = 0 %then %do;
			%put ERROR: The driver file does not exist 
			Aborting analysis;
		%goto ExitNow;	
	%end;

 	proc import datafile = "&Driver." out = Table1.driver (where = (not missing(Variable_Name)))
		dbms = xlsx replace;
		sheet = "Variables";
	run;

	/* Driver prep work */
	data Table1.driverb;
		set Table1.driver (keep = Variable_name Label type);
		Order = _N_; /* Establish order for output */
		
		/* Create upcase version of original name */
		name_up = upcase(variable_name);
	run;

	/* Check counts of variables */
	data _null_;
		set Table1.driver;
		retain CatCount ContCount ;
		if _N_ = 1 then do;
			CatCount = 0;
			ContCount = 0;
		end;
		if upcase(Type) = "CATEGORICAL" then CatCount +1;
		if upcase(Type) = "CONTINUOUS" then ContCount +1;
		call symputx("CatCount", CatCount);
		call symputx("ContCount", ContCount);

	run;

	%put Note: There are &ContCount. continuous variables and  &CatCount. categorical variables;

	/* Set up variable lists */
	%if %eval(&CatCount. >= 1) %then %do;
		proc sql noprint;
			select Variable_Name, 
					upcase(strip(Variable_name))
			into :CATLIST separated by " ",
				:CatListQ separated by '" "'

			from Table1.driver
			where upcase(Type) = "CATEGORICAL";
		quit;
	%end;

	%else %do;
		%let catlist = ;
	%end;

	%if %eval(&ContCount. >= 1) %then %do; 
		/* List of continuous variables */
		proc sql noprint;
			select Variable_Name
			into :NLIST separated by " "
			from Table1.driver
			where upcase(Type) = "CONTINUOUS";

		quit;

	%end;

	%else %do;
		%let NLIST = ;
	%end;

	%put The following categorical variables will be included: &catlist.;
	%put The following numeric variables will be included: &nlist.;


	%if &ByClass = Y %then %do;
		/* Set up standardized naming scheme for class levels - needed for output following transpose */
		proc sort data = &dataset. out = Table1.insort ;
			by &classvar.;
		run;

		data Table1.indata;
			set Table1.insort;
			by &classvar.;

			%if %upcase(&EXCLUDEMISS.) = Y %then %do;
				where not missing(&classvar.);
			%end;

			if first.&classvar then ClassNum +1;

			length StandClass $10;

			StandClass = cats("Class_",ClassNum);

		run;

		/* Generate label statement */
		proc sql noprint;
			select distinct cats(StandClass, "='",&classvar.,"'"), /* For regular data set labeling */
				count(unique(StandClass)) /* count number of class levels */
		
			into :ClassLabel separated by " ",
				:ClassCount
			from Table1.indata;
		quit;

		proc format;
			value $ ClassFmt 
			&classlabel.;
		run;
			
		%put &Classlabel.;
		%put Number Classes = &ClassCount.;
	%end; /* End prep work for class-level analysis */

	%else %do; /* Don't prep class parts if it's only an overall analysis */
		data Table1.indata;
			set &dataset.;
		run;
	%end;

	/* Set up for determining which statistical tests to apply */
	data Table1.testfmt;
		format statistical_test Statistical_test2 $30. Metric metric_up $20.; 
 		set Table1.driver (keep = Variable_Name type Statistical_test Metric rename = (type=DataType));
		where not missing(Variable_name);
		format statistical_test Statistical_test2 $30. Metric metric_up $20.; 
		Start = upcase(strip(Variable_name));
		Type = "C";
		Fmtname = "Test";

		if missing(statistical_test) then statistical_test = "AUTO-SELECT";
		statistical_test2 = upcase(compress(statistical_test," ","p"));

		metric_up = upcase(compress(Metric," ","p"));
		if missing(compress(metric_up)) then metric_up = "AUTOSELECT";

		/* Error checks */
		%if &ByClass. = Y & &DISPLAY_PVAL. = Y %then %do;
			/* Check that statistical test is in list */
			if statistical_test2 not in ("AUTOSELECT", "CHISQUARE", "ANOVA", "KRUSKALWALLIS",
				"KOLMOGOROVSMIRNOV", "TTEST", "WILCOXONRANKSUMS") then do;
				put "WARNING: " Statistical_test "is not a valid option. Defaulting to AUTO-SELECT";
				statistical_test2 = "AUTOSELECT";
			end;
			
			/* Check that statistical test is valid for data type */
			else if (upcase(DataType) = "CONTINUOUS" and statistical_test2 = "CHISQUARE") or
				(upcase(DataType) = "CATEGORICAL" and statistical_test2 in ("ANOVA", "KRUSKALWALLIS",
				"KOLMOGOROVSMIRNOV", "TTEST", "WILCOXONRANKSUMS")) then do;
				put "WARNING: " Statistical_test " is not valid for " DataType " data. Defaulting to AUTO-SELECT.";
				statistical_test2 = "AUTOSELECT";
			end;

			/* Check that test is available for # groups */
			if (&ClassCount. = 2 & statistical_test2 in ("ANOVA", "KRUSKALWALLIS") ) or
				(&ClassCount. > 2 & statistical_test2 in ("TTEST", "WILCOXONRANKSUMS", "KOLMOGOROVSMIRNOV")) then do;
				put "WARNING: " Statistical_test "is not an option for analyses of &ClassCount. groups. Defaulting to AUTO-SELECT";
				statistical_test2 = "AUTOSELECT";
			end;
		%end;

		Label = upcase(Statistical_test2);

		/* Check that metric is valid */
		if ( upcase(DataType) = "CONTINUOUS" and metric_up not in ("MEDIAN", "MEAN", "AUTOSELECT")) or 
			(upcase(DataType) = "CATEGORICAL" and metric_up not in ("COUNT", "AUTOSELECT")) THEN DO;
				metric_up = "AUTOSELECT";
				put "WARNING: Metric for " Variable_name " is invalid. Defaulting to AUTO-SELECT.";
			end;
		fmtname_metric = "Metric";
	run;

	proc format cntlin = Table1.testfmt;
	run;

	proc format cntlin = Table1.testfmt (keep = fmtname_metric start type metric_up rename = (
		fmtname_metric=FMTNAME metric_up = label));
	run;


	/* Check that requested variables exist in the in data set */
	proc sql noprint;
		create table table1.badvars as
		select Variable_Name
		from table1.driver 
		where upcase(Variable_Name) not in (select upcase(name) from
			dictionary.columns where upcase(libname) = "TABLE1" and UPCASE(memname) = "INDATA");
	quit;

	/* Check to see if there are any bad variables (i.e., badvars has more than one row */
	%let dsnid = %sysfunc(open(table1.badvars));

	%if %sysfunc(attrn(&dsnid.,nlobs)) ~= 0 %then %do;
		proc sql noprint;
			select * into :dnelist
			from table1.badvars ;
		quit;

		/* Close the open data set */
		%let rc  =%sysfunc(close(&dsnid.));

		%put ERROR: The following requested variables do not exist in &dataset.: &dnelist.. 
			Aborting analysis;
		%goto ExitNow;	
	%end;

	/* Close the open data set */
	%let rc  =%sysfunc(close(&dsnid.));

	/* Check that the categorical variables are character format - abort if wrong */
	%if &CatCount. >= 1 %then %do;
		proc sql noprint;
			select 
				 sum(case when type = "num" then 1 else 0 end),
					cats(case when type = "num" then name end)
			into :BadVarCount,
				:BadVars separated by " "
			from dictionary.columns
			where upcase(libname) = "TABLE1" and UPCASE(memname) = "INDATA" and upcase(name) in ("&CatListQ.");
		quit;

		%if &BadVarCount. > 0 %then %do;
			%put ERROR: Categorical analysis requested for the following numeric variables: &BadVars.. 
				Convert these variables to character type for categorical analysis.
				Aborting analysis;
			%goto ExitNow;	
		%end;
	%end;

	/* Set up format for labeling and ordering variables */
	proc sort data = table1.driverb;
		by name_up;
	run;
	
	/* If no variable labels provided in Excel driver, pull from indata */
	data table1.extralabels (keep = name_up label );
		set  sashelp.vcolumn (keep = libname memname name label);
		where libname = "TABLE1" and memname = "INDATA";
		name_up = upcase(name);
	run;

	proc sort data = table1.extralabels;
		by name_up ;
	run;

	/* merge in existing labels */
	data Table1.labelfmt (drop = order);
		length name_up $32. label $200.;
		merge Table1.driverb (in=a keep = Variable_name name_up Label Order rename = (Label=DriverLabel) )
			table1.extralabels (rename = (label=existlabel));
		by name_up;
		if a;
		Start = upcase(strip(Variable_name));

		if not missing(DriverLabel) then Label = DriverLabel;
			else if not missing(existlabel) then label = existlabel;
			else label = variable_name;

		/* Label */
		Type = "C";
		Fmtname = "Varlab";
		output;
		/* Order */

		Label = input(Order, $200.);
		FmtName = "Order";
		output;

	run;

	proc sort data = Table1.labelfmt;
		by Fmtname;
	run;

	proc format cntlin = Table1.labelfmt;
	run;


	/* ------------------------------------------------------*/
	/*			Start Analysis: Numeric Variables			*/
	/* ------------------------------------------------------*/


    %IF &NLIST NE  %THEN %DO; 

   		/* Run overall stats, even if not requested, to pick up the N's */
			%put NOTE: Running overall statistics for continuous data;

	   		/* Calculate basic stats for all variables */
		   	proc univariate data = Table1.indata normal outtable = Table1.Overall_numsummary (keep =_VAR_  _NOBS_ _NMISS_ _SUM_ _MEAN_ _STD_ _MIN_ 
				_P1_ _P5_ _P10_ _P90_ _P95_ _P99_ _Q1_ _MEDIAN_ _Q3_ _MAX_ _RANGE_ _qRANGE_ _MODE_ _NORMAL_ _PROBN_
				) noprint;
				var &nlist.;
			run;

			data &outlib..AllStats_Numeric_overall (drop = i);
				set Table1.overall_numsummary (rename = (_VAR_ = Variable));

				/* Round */
				array unround (5) _Q1_ _MEDIAN_ _Q3_ _MEAN_ _STD_;
				array rounded (5) r_Q1_ r_MEDIAN_ r_Q3_ r_MEAN_ r_STD_;

				do i = 1 to 5;
					rounded(i) = round(unround(i),&ROUNDTO.);
				end;

				Overall_Result_Median = cat(strip(r_MEDIAN_), " (", strip(r_Q1_), ", ",strip(r_Q3_), ")");
				Overall_Result_Mean = cat(put(r_MEAN_, 10.&deccnt.), " (", strip(put(r_STD_, 10.&deccnt.)), ")");

			run;

			proc sort data = &outlib..AllStats_Numeric_overall ;
				by Variable;
			run;

	
		%if &byclass. = Y %then %do;
			/* Calculate basic stats for all variables */
		   	proc univariate data = Table1.indata normal outtable = Table1.numsummary (keep =_VAR_ StandClass _NOBS_ _NMISS_ _SUM_ _MEAN_ _STD_ _MIN_ 
				_Q1_ _MEDIAN_ _Q3_ _MAX_ _RANGE_ _qRANGE_ _MODE_ _NORMAL_ _PROBN_
				) noprint;
				var &nlist.;
				class StandClass;
				%if %upcase(&ODSGRAPHS.) = Y %then %do;
					histogram &nlist.;
				%end;
			run;

			/* All the tests are run for analyst review - only requested test is included in output table */
			/* Run non-parametric stats */
			proc npar1way data=Table1.indata wilcoxon edf;
				var &nlist.;
				class StandClass;
				output out = Table1.nonpar_tests ;
			run;

			data Table1.nonpar_tests2 (rename = (P_KSA = pvalue_np_uneqvar));
				set Table1.nonpar_tests;
				%if &ClassCount = 2 %then %do;
					rename P2_WIL = pvalue_np_eqvar;
				%end;
				%else %do;
					rename P_KW = pvalue_np_eqvar;
					P_KSA =.;
				%end;
			run;

			/* Run parametric stats */
			/* T-tests: only if class variable has 2 levels */
			%if &CLassCount = 2 %then %do;
				ods output Ttests = Table1.numttests equality = Table1.numeqvar;
				proc ttest data = Table1.indata ;
					var &nlist.;
					class StandClass;
				run;

				/* Choose correct ttest method based on variance */
				proc sort data = Table1.numttests;
					by Variable;
				run;

				proc sort data = Table1.numeqvar;
					by Variable;
				run;

				data Table1.Parm_tests (drop = DF  rename = (Method = TTestMethod Probt = Parm_pvalue
						ProbF = probf_variance));
					merge Table1.numttests Table1.numeqvar (keep = Variable ProbF) ;
					by Variable;
					if (ProbF < 0.05 and variances = "Unequal") or (ProbF > 0.05 and Variances = "Equal");
					ParmTest = "T test";
					label ProbF = "F Test for Equal Variance";
				run;
			%end;

			%else %if &ClassCount >= 3 %then %do;
			
				/*----	This section iterates through each  variable ----*/
				/* Create ANOVA driver data set */
				data Table1.driver_con;
					set Table1.driverb;
					where upcase(Type) = "CONTINUOUS" ;
					Counter +1;
					call symputx('totparm', Counter);
				run;

				%let parmsets = ; /* this will be a list of all the final data sets that need to be combined */

				%let a = 1;
				%do %while (&a. <= &totparm.);

					data _null_;
						set Table1.driver_con;
						where counter = &a.;

						/* Set up a short name so as to avoid issues with data set names being too long */
						if length(strip(Variable_name)) > 25 then ShortName = cats(substr(Variable_name, 1,15),&a.);
							else ShortName = Variable_name;

						call symputx("varcon", Variable_name);
						call symputx("varcon_s", ShortName);
					run;

					/* Run the ANOVA */
					ods table ModelANOVA   = Table1.Anova_&varcon_s. HOVFTest=Table1.ftest_&varcon_s.;
					proc glm data = Table1.indata;
						class &classvar.;
						model &varcon. = &classvar.;
						means &classvar. / hovtest ;
					run;
					quit;

					/* Merge variance test with ANOVA results */
					data Table1.Anova_&varcon_s.2 (drop = Source);
						merge Table1.Anova_&varcon_s. Table1.ftest_&varcon_s. (keep = dependent
							ProbF Source rename = (ProbF=probf_variance) where =(Source ne "Error" ));
						by dependent;
						label probf_variance = "Levene's Test P-Value";
					run;

					/* Add to list of data sets to stack at end */
					%let parmsets = &parmsets. Table1.Anova_&varcon_s.2;

					/* Iterate to next variable */
					%let a = %eval(&a. +1);

				%end; /* End variable loop for ANOVA  */

				/* Combine ANOVA output for all variables */
				data Table1.Parm_tests (drop = HypothesisType rename = (ProbF = Parm_pvalue Dependent = Variable));
					length Dependent $32.;
					set &parmsets.;
					if HypothesisType = 1; /* Doesn't matter which one b/c it's a one-way ANOVA */
					ParmTest = "ANOVA";
				run;

			%end; /* End ANOVA analysis */


			/* Merge stat test results to summary stats */
				proc sort data = Table1.numsummary;
					by _VAR_;
				run;

				proc sort data = Table1.nonpar_tests2;
					by _VAR_;
				run;
		
				proc sort data = Table1.Parm_tests;
					by Variable;
				run;

			/* Save detailed summary table */
			data &outlib..AllStats_Numeric_class (drop = parmtest);

				format StandClass2 $100. Variable $32. StandClass $100.;

				merge Table1.numsummary (rename = (_VAR_ = Variable)) 
					Table1.nonpar_tests2 (rename = (_VAR_ = Variable))
					Table1.Parm_tests;
				by Variable;

				label Parm_pvalue = "Parametric Test p-value";
				StandClass2 = put(StandClass, $Classfmt.);
				label StandClass2 = "&classvar.";
				format Parm_pvalue probf_variance pvalue6.3;

			run;

			%end; /* End class-specific calculations */

			/* Combine overall and by class analysis */
			/* Choose p-value */
			data Table1.numeric_combo ;
				format variable $32. 
					%if &BYCLASS = Y %then %do; StandClass2 $100. %end ;;
				merge 
					&outlib..AllStats_Numeric_overall  (keep = Variable _NOBS_
						Overall_Result_mean Overall_Result_median _PROBN_ rename = (_PROBN_=_PROBN_overall))
						
				%if &BYCLASS = Y %then %do;
					&outlib..AllStats_Numeric_class (drop = _NOBS_ _PROBN_ _NORMAL_  )
				%end;
					;
				by variable;
				format variable $32.;

				StatTest =  put(upcase(strip(Variable)), $test.);
				format Overall_Result $30. Metrics $30.;

				/* Determine which type of result to display */
				if put(upcase(variable), $metric.) = "MEAN" or 
					(put(upcase(variable), $metric.) = "AUTOSELECT" AND _PROBN_overall >= 0.05) then do;
					Overall_Result = strip(Overall_Result_Mean);
					%if &BYCLASS = Y %then %do; 
						Result = cat(strip(put(_MEAN_, 10.&deccnt.)), " (", strip(put(_STD_, 7.&deccnt.)), ")");
					%end;
					Metrics = "Mean (SD)";
				end;

				else do;
					Overall_Result = strip(Overall_Result_Median);
					%if &BYCLASS = Y %then %do; 
						/* Check if the median results need to be rounded */
						Decimals = length(scan(compress(_MEDIAN_), -1, "."));
						if Decimals > &deccnt. then Result = cat(strip(put(_MEDIAN_,10.&deccnt.))," (", strip(put(_Q1_, 10.&deccnt.)), ", ",strip(put(_Q3_, 10.&deccnt.)),")");	
							else Result = cat(strip(_MEDIAN_)," (", strip(_Q1_), ", ",strip(_Q3_),")");
					%end;
					Metrics = "Median (Q1, Q3)";
				end;

				%if %upcase(&BYCLASS) = Y & %upcase(&DISPLAY_PVAL.) = Y %then %do;
					format  AutoSelectTest FinalTest $50.;
					/* Auto-selection for most appropriate test */
					if _PROBN_overall >= 0.05 then do; /* Parametric test appropriate*/
						if &ClassCount = 2 then AutoSelectTest = "TTEST";
						else if &ClassCount. >2 and probf_variance >= 0.05 then AutoSelectTest = "ANOVA";
						else if &ClassCount. >2 and probf_variance < 0.05 then AutoSelectTest = "OUT OF SCOPE" ;
					end; /* end logic for normal distribution */
					else if _PROBN_overall < 0.05 then do; /* Non-Normal */
						if &ClassCount = 2 and probf_variance >= 0.05 then  AutoSelectTest = "WILCOXONRANKSUMS";
						else if &ClassCount = 2 and probf_variance < 0.05 then AutoSelectTest = "KOLMOGOROVSMIRNOV";
						else if &ClassCount > 2 and probf_variance >= 0.05 then  AutoSelectTest = "KRUSKALWALLIS";
						else if &ClassCount > 2 and probf_variance < 0.05 then AutoSelectTest ="OUT OF SCOPE" ;
					end; /* End logic for non-normal variables */

					/* Compare auto-selected test with user-requested test */
					if StatTest ne "AUTOSELECT" and (AutoSelectTest ne StatTest ) then do;
						put "WARNING: Consider " AutoSelectTest " for " Variable ;
					end;

					if StatTest = "AUTOSELECT" then FinalTest = AutoSelectTest;
						else FinalTest = StatTest;
			
					if FinalTest in ("TTEST", "ANOVA") then Final_pval = Parm_pvalue;
						else if FinalTest in ("KRUSKALWALLIS", "WILCOXONRANKSUMS") then Final_pval = pvalue_np_eqvar;
						%if &ClassCount. = 2 %then %do; 
							else if FinalTest = "KOLMOGOROVSMIRNOV" then Final_pval = pvalue_np_uneqvar;
						%end;
						else if FinalTest = "OUT OF SCOPE" then do;
							put "WARNING: Variable " Variable "requires an out-of-scope test. P-values suppressed";
						end; 
			
				%end;	
				%else %do;
					final_pval = .;
				%end;

			run;

			/* Transpose so class variables go across, if there is by-class analysis */
			%if &BYCLASS = Y %then %do;
				proc transpose data = table1.numeric_combo out = Table1.numeric_t (rename = (final_pval=pvalue));
					by Variable Overall_Result _NOBS_ final_pval Metrics;
					var  Result;
					id StandClass;
				run;
			%end;
			%else %do;
				data Table1.numeric_t;
					set table1.numeric_combo;
				run;
			%end;


	%end; /* End numeric variable analysis */

	/* ------------------------------------------------------*/
	/*					Categorical Variables				*/
	/* ------------------------------------------------------*/

	%if &catlist. ne %then %do;

		/*----	This section iterates through each categorical variable ----*/
		/* Create categorical driver data set that renumbers the categorical variables */
		data Table1.driver_cat;
			set Table1.testfmt (keep = Variable_Name DataType statistical_test2);
			where upcase(DataType) = "CATEGORICAL";
			Counter +1;
		run;

		/* Find number of variables */
		proc sql noprint;
			select max(counter) into :totcats
			from Table1.driver_cat;
		quit;

		%let list = ; /* this will be a list of all the final data sets that need to be combined */
		%let list_overall = ;

		%let c = 1;
		%do %while (&c. <= &totcats.);

			data _null_;
				set Table1.driver_cat;
				where counter = &c.;

				/* Set up a short name so as to avoid issues with data set names being too long */
				if length(strip(Variable_name)) >= 25 then ShortName = cats(substr(Variable_name, 1,15),&c.);
					else ShortName = Variable_name;

				call symputx("var", Variable_name);
				call symputx("varcat_s", ShortName);
			run;

			%put Note: Calculating &var. statistics;

			/* Run overall regardless of options to get overall N's by variable */
				proc freq data = Table1.indata (where = (not missing(&var.))) ;
					table &var. / out = Table1.ovfreq_&varcat_s. ;
				run;

				/* Manipulate frequency table */
				data Table1.ovfreq2_&varcat_s. (rename = (&var. = Category));
					retain Variable;
					set Table1.ovfreq_&varcat_s.;
					/* combine count and percent into single variable */
					Overall_Result = cat(count, " (",strip(put(PERCENT,7.&deccnt.)), "%)");
					Variable = "&var.";
				run;

				%let varlevels = &sysnobs.;

				%let list_overall = &list_overall. Table1.ovfreq2_&varcat_s.;

			/*---- Run by class statistics ----*/
			%if &byclass. = Y %then %do;

				/* Determine requested tests */
				data _NULL_;
					set Table1.driver_cat;
					where counter = &c.;
					call symputx('CATTEST', Statistical_Test2);
				run;

				/* Calculate stats */
				proc freq data = Table1.indata (where = (not missing(&var.))) ;
					table &var.*StandClass / out = Table1.freq_&varcat_s. outpct chisq warn = output;
					output out = Table1.chisq_&varcat_s. chisq N  ;
				run;

				%if %sysfunc(exist(Table1.chisq_&varcat_s.)) = 0 %then %do;

					/* Create blank table with initialized variables if the chisq output was not produced
					   due to lack of variation in response */
					data Table1.chisq_&varcat_s.2;
						WARN_PCHI = .;
						P_PCHI= .;
						pvalue =.;
					run;
				%end;

				%else %do;

					/* Determine if exact test is warranted */
					data _null_;
						set Table1.chisq_&varcat_s.;
						call symputx("NeedExact", WARN_PCHI);
					run;

					/* Run/Use Fisher's exact test */

					%if &NeedExact = 1 & &CATTEST. = AUTOSELECT & &DISPLAY_PVAL. = Y %then %do;
						proc freq data = Table1.indata (where = (not missing(&var.))) ;
							table &var.*StandClass / out = Table1.freq_&varcat_s. outpct chisq exact warn = output;
							output out = Table1.chisq_&varcat_s. chisq N fishers ;
						run;

						data Table1.chisq_&varcat_s.2;
							set Table1.chisq_&varcat_s.;
							format pvalue pvalue6.3;
							pvalue = XP2_FISH;
							FinalTest = "Fisher's Exact";
						run;

					%end;

					/* Use CHISQ */
					%else %do;
						%if &NeedExact = 1 & &CATTEST. = CHISQUARE & &DISPLAY_PVAL. = Y %then %do;
							%put WARNING: Chi square test inappropriate for data due to small cell scounts. To 
								run and use Fishers Exact test, set driver option to AUTO-SELECT;	
						%end; 

						data Table1.chisq_&varcat_s.2;
							set Table1.chisq_&varcat_s.;
							format pvalue pvalue6.3;
							pvalue = P_PCHI;
							FinalTest = "Chi Square";
						run;
					%end;


				%end;

				/* Manipulate frequency table */
				data Table1.freq2_&varcat_s. (rename = (&var. = Category));
					retain Variable;
					set Table1.freq_&varcat_s.;
					/* combine count and percent into single variable */
					NPercent = cat(count, " (",strip(put(PCT_COL,7.&deccnt.)), "%)");
					Variable = "&var.";
				run;

				proc transpose data = Table1.freq2_&varcat_s. out = Table1.freqt_&varcat_s. (drop = _NAME_);
					by Variable Category;
					var NPercent;
					id StandClass;
				run;

				/* Merge with stats */
				data Table1.chisq_&varcat_s.3;
					set Table1.chisq_&varcat_s.2;
					Variable = "&var.";
				run;

				data Table1.freq3_&varcat_s.;
					merge Table1.freqt_&varcat_s. Table1.chisq_&varcat_s.3;
					format pvalue pvalue8.3;

					by Variable;

				run;

				/* Add to list of data sets to stack at end */
				%let list = &list. Table1.freq3_&varcat_s.;

			%end; /* Closes "by class" segment */

			/* Iterate to next variable */
			%let c = %eval(&c. +1);

		%end; /* End categorical loop */


		/* Stack Overall data sets */
		data &outlib..allstats_cat_overall ;
			length Variable $32 Category $100 ;
			format Variable $32. Category $100. ;
			set &list_overall.;

			if missing(Overall_Result) then Overall_Result = "0 (0.00%)";
			Metrics = "N (%)";
		run;

		/* Counts of non-missing values */
		proc means data = &outlib..allstats_cat_overall noprint nway;
			output out = Table1.char_Ns
			sum(COUNT)=;
			class Variable;
		run;

		proc sort data = &outlib..allstats_cat_overall ;
			by Variable Category;
		run;


		/* Stack by class data sets */
		%if &byclass. = Y %then %do;
			/* Create single data set with all categorical information */
			data &outlib..allstats_cat_class ;
				length Variable $32 Category $100 FinalTest $40;
				format Variable $32. Category $100. FinalTest $40.;
				set &list.;

				Metrics = "N (%)";

				/* Fill in zero cells */
				array zerofill (*) class:;
				do i = 1 to hbound(zerofill);
					if missing(zerofill(i)) then zerofill(i) = "0 (0.00%)";
				end;

				label &classlabel.;

			run;

			proc sort data =  &outlib..allstats_cat_class;
				by Variable Category;
			run;

		%end;

		data Table1.Allcat_a;
			merge
			%if &overall. = Y %then %do;
				&outlib..allstats_cat_overall (keep = Variable Category Overall_Result Metrics)
			%end;
			%if &byclass. = Y %then %do;
				&outlib..allstats_cat_class (keep = Variable Category Metrics Class_: pvalue)
			%end;
			;
			by Variable Category;
		run;

		/* Add N's */
		data Table1.Allcat;
			merge Table1.allcat_a 	Table1.char_Ns (keep = variable count rename = (Count = _NOBS_));
			by variable;
		run;


   %end; /* End categorical analysis */


   /*---------------------------------------------------*/
   /*----		Print for Analyst Review			----*/
   /*---------------------------------------------------*/

   ods html;
	%IF &NLIST NE  %THEN %DO; 

		%if &byclass. = Y  %then %do;
		   proc print data = Table1.numeric_combo (keep = StandClass2 variable _NOBS_ _NMISS_ _MEAN_ _STD_ _MIN_
				_Q1_ _MEDIAN_ _Q3_ 	_MAX_ _MODE_ probf_variance %if &DISPLAY_PVAL. = Y %then %do; FinalTest %end;) label noobs;
				format StandClass2 $50. probf_variance pvalue8.3;
			run;
		%end;

		%if &overall. = Y %then %do;
		   proc print data = &outlib..AllStats_Numeric_overall (keep = variable _NOBS_ _NMISS_ _MEAN_ _STD_ _MIN_
				_Q1_ _MEDIAN_ _Q3_ 	_MAX_ _MODE_ _PROBN_) label noobs;
				format _PROBN_ pvalue8.3;
			run;
		%end;

	%end;


   /*---------------------------------------------------*/
   /*----			Prep for Output					----*/
   /*---------------------------------------------------*/

   	/* Generate label for proc report */
	%if &byclass. = Y %then %do;
		proc sql noprint;
			select 
				distinct cats(StandClass, "='",&classvar.,"~ N=", count(StandClass), "'")  /* For proc report column headers */
			into :ReportLabel separated by " "
			from Table1.indata
			group by &classvar.;
		quit;
	%end;

	/* Get the overall observation count */
	data _null_;
		set Table1.indata;
		call symputx('totalobs',_N_);
	run;

   /* Combine character and numeric results */
   data Table1.combo (drop = Variable rename = (Variable2=Variable));
   		format Variable2 $200. %if &CatCount. > 0 %then %do; Category %end; Class_: $100. Metrics $30.;
		%if &Byclass = Y %then %do;
			format pvalue pvalue8.3;
		%end;

   		set 
			%if &CatCount. > 0 %then %do;
				Table1.Allcat 
			%end;
			%if &ContCount. > 0 %then %do;
				Table1.numeric_t ;
			%end;
			;

		/* Add observation counts to header */
		label 
			/* Apply class labels */
			%if &byclass. = Y %then %do;
				&ReportLabel
			%end;
			
			%if &overall. = Y %then %do;
				Overall_Result = "Overall~N=&totalobs."
			%end;
			;
		Order = input(put(strip(upcase(Variable)), $order.), 8.);
		Variable2 = put(strip(upcase(Variable)),$varlab.); 

	run;

	proc sort data = Table1.combo;
		by Order;
	run;

	/*---------------------------------------------------*/
   /*----			Create RTF					----*/
   /*---------------------------------------------------*/

ODS PATH WORK.TEMPLAT(UPDATE)
   SASUSR.TEMPLAT(UPDATE) SASHELP.TMPLMST(READ);

   PROC TEMPLATE;
	   DEFINE STYLE STYLES.TABLES;
	   NOTES "MY TABLE STYLE"; 
	   PARENT=STYLES.MINIMAL;

	     STYLE SYSTEMTITLE /FONT_SIZE = 12pt     FONT_FACE = "TIMES NEW ROMAN";

	     STYLE HEADER /
	           FONT_FACE = "TIMES NEW ROMAN"
	            CELLPADDING=8
	            JUST=C
	            VJUST=C
	            FONT_SIZE = 10pt
	           FONT_WEIGHT = BOLD; 

	     STYLE TABLE /
	            FRAME=HSIDES            /* outside borders: void, box, above/below, vsides/hsides, lhs/rhs */
	            RULES=GROUP              /* internal borders: none, all, cols, rows, groups */
	            CELLPADDING=6            /* the space between table cell contents and the cell border */
	            CELLSPACING=6           /* the space between table cells, allows background to show */
	            JUST=C
	            FONT_SIZE = 10pt
	            BORDERWIDTH = 0.5pt;  /* the width of the borders and rules */

	     STYLE DATAEMPHASIS /
	           FONT_FACE = "TIMES NEW ROMAN"
	           FONT_SIZE = 10pt
	           FONT_WEIGHT = BOLD;

	     STYLE DATA /
	           FONT_FACE = "TIMES NEW ROMAN" 
	           FONT_SIZE = 10pt;

	     STYLE SYSTEMFOOTER /FONT_SIZE = 9pt FONT_FACE = "TIMES NEW ROMAN" JUST=C;
	   END;

   RUN; 

   *------- build the table -----;

   OPTIONS ORIENTATION=PORTRAIT MISSING = "-" NODATE;

     ODS RTF STYLE=tables FILE= "&OUTPATH.\&FNAME &SYSDATE..DOC"; 


	PROC REPORT DATA=Table1.combo HEADLINE HEADSKIP CENTER STYLE(REPORT)={JUST=CENTER} SPLIT='~' nowd 
	          SPANROWS LS=256;
	      COLUMNS order variable 

 			%if &CatCount. > 0 %then %do; category %end;
			%if &DISPLAY_N. = Y %then %do; _NOBS_ %end;
			%if &overall. = Y %then %do;
				Overall_Result
			%end;
			%if &byclass. = Y %then %do;
				Class: 
				%if &DISPLAY_PVAL = Y %then %do; pvalue %end;
			%end;
			%if &DISPLAY_METRIC = Y %then %do; metrics %end;

			;	

	      DEFINE order/order order=internal noprint;
	      DEFINE variable/ Order order=data  "Variable"  STYLE(COLUMN) = {JUST = L CellWidth=15%};

			%if &CatCount. > 0 %then %do;
				DEFINE category/ DISPLAY   "Level"   STYLE(COLUMN) = {JUST = L CellWidth=15%};
			%end;

			%if &DISPLAY_N. = Y %then %do; DEFINE _NOBS_ / "N" order style(Column) = {JUST = C }; %end;

			%if &overall. = Y %then %do;
				DEFINE Overall_Result / DISPLAY STYLE(COLUMN) = {JUST = C } ;
			%end;

			%if &byclass. = Y %then %do;
			  %let z = 1;
			  
			  %do %while (&z. <= &ClassCount.);
			  	DEFINE Class_&z. / DISPLAY STYLE(COLUMN) = {JUST = C } ;
		     	%let z = %eval(&z. +1);
			  %end;

			   %if &DISPLAY_PVAL. = Y %then %do;
			      /* Bold p-values under 0.05 */
			       DEFINE pvalue/ORDER MISSING "P-Value" STYLE(COLUMN)={JUST = C CellWidth=10%} ;

		         COMPUTE pvalue; 
		              IF . < pvalue <0.05 THEN 
		               CALL DEFINE("pvalue", "STYLE", "STYLE=[FONT_WEIGHT=BOLD]");
		         ENDCOMP; 
				%end; /* End p-value column stuff */

			%end; /* End Class Variable Columns */
	       
		%if &DISPLAY_METRIC. = Y %then %do; DEFINE Metrics/ DISPLAY    STYLE(COLUMN) = {JUST = C }; %end;

	     compute after variable; line ''; endcomp; /* Inserts a blank line after each variable */
	       
	   RUN; 

 
      ODS RTF CLOSE; 

	/* Get rid of temp files created by macro in Table1 directory */
	%if %upcase(&CLEARTEMP.) = Y %then %do;
		proc datasets lib=Table1 nolist  ;
			save combo;
		run;
		quit;
	%end;

	  %ExitNow:

%mend; 
Reeza
Super User

That's how you call the macro, can you please provide the macro definition?

There does not appear to be a parameter to include col/row percent so the change to the code may be small or large. 
Have you attempted to modify the macro definition?

 

 

 


@UcheOkoro wrote:

Hello ,

 

Please, I need help with a SAS macro command that would give me row percent for descriptive tables. I am presently getting column percent which is not what I want. Below is my sas macro.

 

Thank you .

 

%TABLE1NDONE(
DATASET = stacked22, /* Input data set */
DRIVER = R:\Research\ Analysis Folder\Table1nDoneDrive.xlsx,
OUTPATH= R:\Research\ Analysis Folder\ , /* File pathway for RTF output */
FNAME= Table 1 SS Demographics by Confidence, /* File name for RTF output */
OVERALL = Y, /* Include overall summary statistics */
BYCLASS = Y, /* Include summary statistics by group */
CLASSVAR = confident_rc1, /* Class variable */
DISPLAY_PVAL = Y, /* Display p-values in RTF report */
DISPLAY_METRIC = N, /* Display metric names in RTF report */
DISPLAY_N = Y /* Display non-missing counts in RTF report */
);

 


 

UcheOkoro
Lapis Lazuli | Level 10

I cannot seem to find where to do the modification.

Tom
Super User Tom
Super User

@UcheOkoro wrote:

I cannot seem to find where to do the modification.


Did you ask Martha?

Title: Table1nDone Macro
Date: 2019/10/02
Author: Martha Wetzel
UcheOkoro
Lapis Lazuli | Level 10
I don't have her contact
Reeza
Super User
Find where it calculates the percentages and where it combines them into one cell. The code is fairly well commented.
The usual procs to do percentages are PROC FREQ.
UcheOkoro
Lapis Lazuli | Level 10

I found the portion of code that computes the percentage and made the change from column to row and it worked. The specific portion is highlighted in red.

Thank you all for the help.

 

		data Table1.freq2_&varcat_s. (rename = (&var. = Category));
					retain Variable;
					set Table1.freq_&varcat_s.;
					/* combine count and percent into single variable */
					NPercent = cat(count, " (",strip(put(PCT_ROW,7.&deccnt.)), "%)");
					Variable = "&var.";
				run;

				proc transpose data = Table1.freq2_&varcat_s. out = Table1.freqt_&varcat_s. (drop = _NAME_);
					by Variable Category;
					var NPercent;
					id StandClass;
				run;
Reeza
Super User
Nicely done! Note that you've changed this macro definition so if you use it again, it will report row again. You may want to keep the versions of code separated somehow 🙂
UcheOkoro
Lapis Lazuli | Level 10

Yes, I now have two versions of macro saved with different names. One for row percent and the other for column percent.

 

Thank you.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1108 views
  • 5 likes
  • 4 in conversation