proc freq and transpose?

[monday89]

Errors with proc freq

Current dataset

id	sex	ethnic	group
1	male	hispanic	yes
2	male	not hispanic	yes
3	female	hispanic	yes
4	female	hispanic	no
5	female	not hispanic	no

I want to get each variable (sex and ethnic) by treatment group

ods output onewayfreqs=c.demographics_freq;
proc freq data=c.demographics;
by group;
tables sex ethnic race ;
run;quit;
ods listing ;

but when i run it it looks like this:

group	table	sex	frequency	percent	cumulative frequency	cumulative	ethnic	ethnic
no	table sex	female					hispanic
no	table sex	male					not hispanic
yes	table sex	female					hispanic
yes	table sex	male					hispanic	not hispanic
yes	table sex	male						not hispanic

I wanted more of as:

group	table	_LABEL_	frequency	percent	cumulative frequency	cumulative
no	table sex	female
no	table sex	male
yes	table sex	female
yes	table sex	male
yes	table ethnic	hispanic
yes	table ethnic	not hispanic
no	table ethnic	hispanic
no	table ethnic	not hispanic

Help!

[ed_sas_member]

Hi @monday89 

Do you want to output a report or a dataset?

In your code, you mention the RACE variable. Or it is not in the entry dataset. Is it a mistake?

Thank you for the clarifications.

Based on the input data, you can try this:

data demographics;
	input id 1 sex $ 3-8 ethnic $ 10-21 group $ 23-25;
	datalines;
1 male   hispanic     yes
2 male   not hispanic yes
3 female hispanic     yes
4 female hispanic     no
5 female not hispanic no
;
run;

proc sort data=demographics;
	by group;
run;

ods output onewayfreqs=demographics_freq_sex;
	proc freq data=demographics;
		by group;
		tables sex;
	run;
ods listing;

ods output onewayfreqs=demographics_freq_ethnic;
	proc freq data=demographics;
		by group;
		tables ethnic;
	run;
ods listing;

data want;
	length _LABEL_ $20.;
	set demographics_freq_sex 	 (rename=(sex=_LABEL_) drop=F_sex)
		demographics_freq_ethnic (rename=(ethnic=_LABEL_) drop=F_ethnic);
run;

proc print data=want;
run;

[monday89]

Yes, sorry it is mistake. No race to be reported. I prefer it to be a dataset. THANK YOU! 

[PaigeMiller]

PROC FREQ has only limited ability to produce custom data sets.

A little programming will get what you want. Here is an example using SASHELP.CLASS

proc freq data=sashelp.class;
tables sex age;
run;
data want;
    length category $ 8;
    set demographics_freq1;
    if strip(table)='Table Sex' then category=f_sex;
    else category=f_age;
    drop age sex f_age f_sex;
run;
        

[ed_sas_member]

Hi @monday89 

Thank you for the prompt answer 

I have updated my previous post with the code. Let me know!

[Ksharp]

You want this ?

data demographics;
	input id 1 sex $ 3-8 ethnic $ 10-21 group $ 23-25;
	datalines;
1 male   hispanic     yes
2 male   not hispanic yes
3 female hispanic     yes
4 female hispanic     no
5 female not hispanic no
;
run;
ods select none;
ods output  list=want;
proc freq data=demographics ;
table group*(sex ethnic)/list sparse;
run;
ods select all;