proc freq or some other option

[monday89]

I have the following table below

patientid	trt	gender	race	cad_history
1	group 1	F	white	1
2	group 2	F	white	1
3	group 1	M	asian	0
4	group 1	F	black	1
5	group 2	M	asian	0

proc sort data=work.test;
by trt;
run;

ods output onewayfreqs=work.test2;
proc freq data=work.test;
table gender race cad_history
/missing;
by trt ;
run;
ods listing;

I get this:

trt	Table	gender	gender	Frequency	Percent	Cumulative Frequency	CumulativePercent	race	race	cad_history	cad_history
group 1	Table gender	F	F	2	66.67	2	66.67				.
group 1	Table gender	M	M	1	33.33	3	100				.
group 1	Table race			1	33.33	1	33.33	asian	asian		.
group 1	Table race			1	33.33	2	66.67	black	black		.
group 1	Table race			1	33.33	3	100	white	white		.
group 1	Table cad_history			1	33.33	1	33.33			0	0
group 1	Table cad_history			2	66.67	3	100			1	1
group 2	Table gender	F	F	1	50	1	50				.
group 2	Table gender	M	M	1	50	2	100				.
group 2	Table race			1	50	1	50	asian	asian		.
group 2	Table race			1	50	2	100	white	white		.
group 2	Table cad_history			1	50	1	50			0	0
group 2	Table cad_history			1	50	2	100			1	1

But then I have to polish it more to get this:

trt	group 1	group 1
F	number (%)	number (%)
M	number (%)	number (%)
white	number (%)	number (%)
asian	number (%)	number (%)
black	number (%)	number (%)
cad history 1	number (%)	number (%)
cad history 0	number (%)	number (%)

Is there a simple way to do this? rather than writing additional commands?

Thanks

[PaigeMiller]

---

@monday89 wrote:

But then I have to polish it more to get this:

trt	group 1	group 1
F	number (%)	number (%)
M	number (%)	number (%)
white	number (%)	number (%)
asian	number (%)	number (%)
black	number (%)	number (%)
cad history 1	number (%)	number (%)
cad history 0	number (%)	number (%)

Is there a simple way to do this? rather than writing additional commands?

Thanks

---

I guess this depends on your definition of "simple". Yes it is possible. In my opinion, generating this exact custom formatted table isn't that simple. Certainly, some of the advanced members of this group (maybe even me) can do it, but would some other easier-to-create table be acceptable?

[monday89]

Yes, easier-to-create table will be acceptable. I am sure it is going to be much better than mine!

[PaigeMiller]

proc freq data=test;
    table gender*trt race*trt cad_history*trt/nocol nopercent;
run;

[ballardw]

Providing and actual example of starting data really helps provide something close to tested code.

I would start with something similar to

Proc tabulate data=have;
   class  trt gender race cat_history;
   tables gender race cat_history,
          trt*(n pctn)
   ;
run;

This does not and is not intended to place multiple values into a single "cell". However with style options and custom formats and such you can make something look like that. The important question would become which do you want: relatively easy to create the contents accurately or put values into a, (personal opinion), complicated to maintain ugly format

[ed_sas_member]

Hi @monday89 

Here is another attempt to do this and output a dataset.

Proc report would be a good approach too.

data have;
	infile datalines dlm="09"x;
	input patientid	trt	$ gender $	race $	cad_history;
	datalines;
1	group 1	F	white	1
2	group 2	F	white	1
3	group 1	M	asian	0
4	group 1	F	black	1
5	group 2	M	asian	0
;
run;

options missing='0';

proc sort data=have;
	by trt;
run;

proc freq data=have noprint;
	table gender/ out=have_stat_1 missing;
	table race  / out=have_stat_2 missing;
	table cad_history / out=have_stat_3 missing; 
	by trt;
run;

data have_stat (keep=flag category count percent trt);
	set have_stat_:;
	length category $ 20;
	if gender ne '' then do;
			category=gender;
			flag=1;
		end;
	else if race ne '' then do;
			category=race;
			flag=2;
		end;
	else if cad_history ne '' then do;
			category=catx(" ","cad_history",cad_history);
			flag=3;
		end;
run;

proc sort data=have_stat;
	by flag category;
run;

proc transpose data=have_stat out=want_tr1 (drop=_:) prefix=c_group;
	var count;
	by flag category;
run;

proc transpose data=have_stat out=want_tr2 (drop=_:) prefix=p_group;
	var percent;
	by flag category;
run;

data want (keep= category group1 group2 rename=(category=trt));
	merge want_tr1 want_tr2;
	by flag category;
	group1 = strip(c_group1)||' ('||strip(round(p_group1,.1))||'%)';
	group2 = strip(c_group2)||' ('||strip(round(p_group2,.1))||'%)';
run;

[Reeza]

Use this code, replacing the PROC FREQ with your own code. First test it and see if it gives you a table you want, should run just as is.

https://gist.github.com/statgeek/e0903d269d4a71316a4e

/*This code is an example of how to generate a table with 
Variable Name, Variable Value, Frequency, Percent, Cumulative Freq and Cum Pct
No macro's are required
Use Proc Freq to generate the list, list variables in a table statement if only specific variables are desired
Use ODS Table to capture the output and then format the output into a printable table.
*/

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sashelp.class;
	table sex age;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc print data=want(obs=20) label;
run;

---

@monday89 wrote:

I have the following table below

patientid	trt	gender	race	cad_history
1	group 1	F	white	1
2	group 2	F	white	1
3	group 1	M	asian	0
4	group 1	F	black	1
5	group 2	M	asian	0

proc sort data=work.test;
by trt;
run;

ods output onewayfreqs=work.test2;
proc freq data=work.test;
table gender race cad_history
/missing;
by trt ;
run;
ods listing;

I get this:

trt	Table	gender	gender	Frequency	Percent	Cumulative Frequency	CumulativePercent	race	race	cad_history	cad_history
group 1	Table gender	F	F	2	66.67	2	66.67				.
group 1	Table gender	M	M	1	33.33	3	100				.
group 1	Table race			1	33.33	1	33.33	asian	asian		.
group 1	Table race			1	33.33	2	66.67	black	black		.
group 1	Table race			1	33.33	3	100	white	white		.
group 1	Table cad_history			1	33.33	1	33.33			0	0
group 1	Table cad_history			2	66.67	3	100			1	1
group 2	Table gender	F	F	1	50	1	50				.
group 2	Table gender	M	M	1	50	2	100				.
group 2	Table race			1	50	1	50	asian	asian		.
group 2	Table race			1	50	2	100	white	white		.
group 2	Table cad_history			1	50	1	50			0	0
group 2	Table cad_history			1	50	2	100			1	1

But then I have to polish it more to get this:

trt	group 1	group 1
F	number (%)	number (%)
M	number (%)	number (%)
white	number (%)	number (%)
asian	number (%)	number (%)
black	number (%)	number (%)
cad history 1	number (%)	number (%)
cad history 0	number (%)	number (%)

Is there a simple way to do this? rather than writing additional commands?

Thanks

---