topic Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y? in SAS Procedures

Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

kjowers — Mon, 17 Jul 2017 21:48:42 GMT

I feel like this should be really easy and obvious, but I can't figure it out. Any advice is appreciated!

It starts with just a simple crosstab. I need the output to be a list of values of x (number of events) for each level of y (regions), of which there are 13. There are 4 types of events, and I need a table to summarize each. Occasionally, there are 0 events in a region. However, I need the output to list all 13 regions. Is there some ridiculously simple solution that I've just overlooked? I tried the sparse option, and it didn't work.

proc freq data = have noprint;
    table region * num_events / nopercent norow nocol
    out = want;
run;

Thank you!!!!

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

ballardw — Mon, 17 Jul 2017 22:52:57 GMT

Can you at least show what the result is expected to look like?

It actually is not "ridiculously easy" easy to display values that do not appear in your data.

Depending on what I wanted for appearance I would likely create a custom format to display your region values and then use one of the procedures that can use Preloadfmt option.

Since you are creating an output data set you could use the SPARSE option if you also use the LIST option on the tables statement.

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

Astounding — Tue, 18 Jul 2017 02:31:21 GMT

Does NUM_EVENTS really represent the type of event? If not, then what is the right variable name?

Whatever the proper name, it's easier to add the missing categories in later. I can't test this right now, but it should create a shell with all the categories:

proc sql;

create table shell as

select distinct a.region, distinct b.num_events from have a, have b;

quit;

My SQL isn't super strong, but that should work. Then merge it back in with the PROC FREQ results:

proc sort data=shell;

by region num_events;

run;

data really_want;

merge want (in=in1) shell;

by region num_events;

if in1=0 then do;

count=0;

percent=0;

end;

run;

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

Ksharp — Tue, 18 Jul 2017 13:19:42 GMT

Better post some sample data to explain your question.

1)try SPARSE option

table region * num_events /sparse ;

2) try ZERO option

weight count/zero ;

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

kjowers — Tue, 18 Jul 2017 14:44:54 GMT

Thanks, @ballardw - at least I don't feel quite so dumb.

Here's what I'm looking for at the minimum:

If the output dataset ends up including percent, col, and row, etc., that's cool. I have a second .xls with formulas reading in the values in the num_events column, ordered by region, summarizing 2 decades' worth of this monthly data by year.

For some reason, when I use list and sparse like so:

proc freq data = have;
	where provider_type in ('Illegally-Operating');
		table region * num_events / list
			out = want sparse;
run;

this is my output.

What gives? 🙂

Thank you!!!

Keri

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

Reeza — Tue, 18 Jul 2017 14:52:28 GMT

Computers are dumb. If it's not in the data at all, how is SAS supposed to know it even exists? Obviously you need to tell it in some manner, and the way to specify that is using a FORMAT or a CLASS data set with ALL the levels/combinations needed. If you search PRELOADFMT and PROC TABULATE you'll see many ways of getting this done.

http://support.sas.com/resources/papers/proceedings11/087-2011.pdf

http://support.sas.com/resources/papers/proceedings11/239-2011.pdf

https://communities.sas.com/t5/Base-SAS-Programming/Proc-Tabulate-creating-a-row-of-zeros-for-nonexistent-variable/m-p/331286/highlight/true#M74437

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

kjowers — Tue, 18 Jul 2017 15:39:09 GMT

Hi @Ksharp,

Thanks for your help!

Here's a sample of my data. There are other variables, but I don't use them here.

When I run:

proc freq data = have;
     where type in ('illegal');
	table region * num_events_1 / sparse
	out = want1;
run; 

proc export data = want
	outfile = '\want.xls'
	dbms = excel replace;
	sheet = 'want1';
run;

this is my output:

The only problem is that the values in 'count' are for the entire 'have' dataset, not just the type = 'illegal' subset. Any idea why? I'm not familiar with how 'sparse' works.

Thanks!!!

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

Reeza — Tue, 18 Jul 2017 15:42:41 GMT

Please post your code AND data as text, otherwise you're asking us to type out your data and code.

There are instructions here on how to include your data as a data step so it can be used immediately in SAS

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

kjowers — Fri, 21 Jul 2017 16:38:24 GMT

Thanks, @Reeza! Will do! That makes total sense, and I can see how that could be a royal pain. I'll attach my syntax and data as files to a previous reply - I'm having a hard time getting the syntax you linked to to work for me (it's definitely me, not the syntax). It looks really cool and helpful, so I'll definitely take more time later to try to figure out how it's working. Thanks again!!!

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

kjowers — Fri, 21 Jul 2017 16:55:55 GMT

Hi,

Attached are my data and syntax files. I've also pasted the syntax below. The issue is that I need the last 2 sheets on the exported .xls file to list all 13 regions, with 0 values as appropriate, rather than excluding the regions with 0 values. I can't seem to get the list/sparse combo to work - it outputs the total sample, not the subsetted samples.

/*** import current month's data ***/

proc import out = have_master
	datafile = 'W:\Have\have.xls' 
	dbms = excel replace;
	getnames = yes;
run;


/*** assign region ***/

data have_1;
	set have_master;

	format region 2.;
	informat region 2.;

	if county in (2)			then region = 1;
	if county in (30)			then region = 2;
	if county in (3)			then region = 3;
	if county in (16)			then region = 4;
	if county in (15)			then region = 5;
	if county in (13)			then region = 6;
	if county in (1,11,21)		then region = 7;
	if county in (5,9,14,17,20)	then region = 8;
	if county in (19,22,23)		then region = 9;
	if county in (4,8,18)		then region = 10;
	if county in (7,12)			then region = 11;
	if county in (10)			then region = 12;
	if county in (6)			then region = 13;

run;


/*** create output datasets ***/

	/** subset variables **/

data have_2;
	set have_1;

	format lh 1.;
	format lc 1.;
	format ulh 1.;
	format ulc 1.;

	if type in ('registered')	then lh = 1;
	if type in ('licensed')		then lc = 1;
	if type in ('illegal') and illegal_type in ('h')
								then ulh = 1;
	if type in ('illegal') and illegal_type in ('c')
								then ulc = 1;

run;

	/** tables **/

proc freq data = have_2;
	where type in ('registered');
		tables lh * region / nopercent norow nocol
			out = lh;
run;

proc freq data = have_2;
	where type in ('licensed');
		tables lc * region / nopercent norow nocol
			out = lc;
run;

proc freq data = have_2;
	where type in ('illegal') and illegal_type in ('h');
		tables ulh * region / nopercent norow nocol
			out = ulh;
run;

proc freq data = have_2;
	where type in ('illegal') and illegal_type in ('c');
		table ulc * region / nopercent norow nocol
			out = ulc;
run;


/*** export output to .xls ***/

proc export data = lh
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'lh';

proc export data = lc
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'lc';

proc export data = ulh
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'ulh';

proc export data = ulc
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'ulc';

run;

Thank you!!!!

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

Reeza — Fri, 21 Jul 2017 17:20:57 GMT

SPARSE/LIST will not work in this case. You need to use PRELOADFMT or CLASSDATA instead.

If the data is not there, SAS cannot know it exists unless you tell it somehow. That 'somehow' is PRELOADFMT or CLASSDATA.

A quick search of those terms on here or lexjansen.com will illustrate fully worked examples that will show you how to modify your code/process.

@kjowers wrote:

Hi,

/*** import current month's data ***/

proc import out = have_master
	datafile = 'W:\Have\have.xls' 
	dbms = excel replace;
	getnames = yes;
run;


/*** assign region ***/

data have_1;
	set have_master;

	format region 2.;
	informat region 2.;

	if county in (2)			then region = 1;
	if county in (30)			then region = 2;
	if county in (3)			then region = 3;
	if county in (16)			then region = 4;
	if county in (15)			then region = 5;
	if county in (13)			then region = 6;
	if county in (1,11,21)		then region = 7;
	if county in (5,9,14,17,20)	then region = 8;
	if county in (19,22,23)		then region = 9;
	if county in (4,8,18)		then region = 10;
	if county in (7,12)			then region = 11;
	if county in (10)			then region = 12;
	if county in (6)			then region = 13;

run;


/*** create output datasets ***/

	/** subset variables **/

data have_2;
	set have_1;

	format lh 1.;
	format lc 1.;
	format ulh 1.;
	format ulc 1.;

	if type in ('registered')	then lh = 1;
	if type in ('licensed')		then lc = 1;
	if type in ('illegal') and illegal_type in ('h')
								then ulh = 1;
	if type in ('illegal') and illegal_type in ('c')
								then ulc = 1;

run;

	/** tables **/

proc freq data = have_2;
	where type in ('registered');
		tables lh * region / nopercent norow nocol
			out = lh;
run;

proc freq data = have_2;
	where type in ('licensed');
		tables lc * region / nopercent norow nocol
			out = lc;
run;

proc freq data = have_2;
	where type in ('illegal') and illegal_type in ('h');
		tables ulh * region / nopercent norow nocol
			out = ulh;
run;

proc freq data = have_2;
	where type in ('illegal') and illegal_type in ('c');
		table ulc * region / nopercent norow nocol
			out = ulc;
run;


/*** export output to .xls ***/

proc export data = lh
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'lh';

proc export data = lc
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'lc';

proc export data = ulh
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'ulh';

proc export data = ulc
	outfile = 'W:\Have\want.xls'
	dbms = excel replace;
	sheet = 'ulc';

run;

Thank you!!!!

Re: Using proc freq for a crosstab (two-by-two); how do I include all levels of Y?

kjowers — Fri, 21 Jul 2017 17:42:07 GMT

Awesome! Thank you so much!!! I really appreciate your help!