topic Re: Proc freq using SPARSE in SAS Procedures

Proc freq using SPARSE

Paul_NYS — Wed, 04 Dec 2013 16:29:41 GMT

Hi Everyone

I am running a Proc freq statement below with the SPARSE option with the intent of forcing the resulting output to include all values of the ExitMonthCategory4 variable regardless of whether there is a value for the combination of the cnty_name*startyear*ExitMonthCategory4 variables. I thought this was the purpose of SPARSE. However, when I run this statement, it is not returning a row for when ExitMonthCategory4 = 78 (see below). Is there something wrong with my statement or am I misunderstanding SPARSE?

Paul

Statement:

proc freq data=&a1 noprint;

tables cnty_name*startyear*ExitMonthCategory4/out=s6Freq sparse;

run;

Result:

cnty_name startyear ExitMonthCategory4 COUNT PERCENT

3 - Outside New York City 2006 . 4353 .

3 - Outside New York City 2006 6 20 0.3623188406

3 - Outside New York City 2006 12 148 2.6811594203

3 - Outside New York City 2006 18 426 7.7173913043

3 - Outside New York City 2006 24 249 4.5108695652

3 - Outside New York City 2006 30 92 1.6666666667

3 - Outside New York City 2006 36 73 1.3224637681

3 - Outside New York City 2006 42 45 0.8152173913

3 - Outside New York City 2006 48 14 0.2536231884

3 - Outside New York City 2006 54 3 0.0543478261

3 - Outside New York City 2006 60 0 0

3 - Outside New York City 2006 66 2 0.0362318841

3 - Outside New York City 2006 72 1 0.018115942

3 - Outside New York City 2006 81 1 0.018115942

3 - Outside New York City 2007 . 3930 .

3 - Outside New York City 2007 6 10 0.1811594203

3 - Outside New York City 2007 12 97 1.7572463768

3 - Outside New York City 2007 18 360 6.5217391304

3 - Outside New York City 2007 24 238 4.3115942029

3 - Outside New York City 2007 30 103 1.865942029

3 - Outside New York City 2007 36 58 1.0507246377

3 - Outside New York City 2007 42 32 0.5797101449

3 - Outside New York City 2007 48 10 0.1811594203

3 - Outside New York City 2007 54 4 0.0724637681

3 - Outside New York City 2007 60 1 0.018115942

3 - Outside New York City 2007 66 3 0.0543478261

3 - Outside New York City 2007 72 2 0.0362318841

3 - Outside New York City 2007 81 0 0

3 - Outside New York City 2008 . 4009 .

Re: Proc freq using SPARSE

Reeza — Wed, 04 Dec 2013 16:43:22 GMT

It has to exist at least once in your dataset for SAS to know it exists at all.

It doesn't show up in the data displayed, does it show up anywhere?

Re: Proc freq using SPARSE

data_null__ — Wed, 04 Dec 2013 16:46:56 GMT

To get something from nothing you will need the features of PROC MEANS/SUMMARY using either CLASSDATA or PRELOADFMT depending on how you want to do it.

Re: Proc freq using SPARSE

Paul_NYS — Wed, 04 Dec 2013 16:52:57 GMT

No, it doesn't show up in this specific data set actually.

Paul

Re: Proc freq using SPARSE

Paul_NYS — Wed, 04 Dec 2013 16:54:25 GMT

I just want to force a frequency for each combination regardless of whether is a value or not. I will take a look at MEANS or SUMMARY.

Paul

Re: Proc freq using SPARSE

Paul_NYS — Wed, 04 Dec 2013 17:39:37 GMT

I tried variations of the below proc summary statement using both 'completetypes' and 'classadata; and neither are producing a row when ExitMonthCategory4 = 78. Is there something I am missing?

Paul

proc summary classdata=s6AgeRangesRos completetypes;

class cnty_name startyear ExitMonthCategory4;

output out=s6AgeRangesRosF;

run;

Re: Proc freq using SPARSE

ballardw — Wed, 04 Dec 2013 17:46:53 GMT

Yes, the value of 78 has to be in the data. Completetypes only forces combinations of values in the data that may not exist but the value must be there for at least one record. If you want 78 to appear when it doesn't you may need to use a custom format and PRELOADFMT in a procedure that supports that option.

Re: Proc freq using SPARSE

Reeza — Wed, 04 Dec 2013 17:53:17 GMT

Unfortunately Nested formats don't work with PRELOADFMT.

Basically, you need to tell SAS the values you want at least once somehow, a format is one way, classdata is another. If you have a dataset that has all possible values there are others.

proc format;

value sample_fmt

20 = 20

other=[8.];

value test_fmt

12=12

13=13

14=14

15=15

16=16

17=17

18=18

19=19

20=20;

run;

proc means data=sashelp.class completetypes;

class age /preloadfmt;

var weight;

format age sample_fmt.;

run;

proc means data=sashelp.class completetypes;

class age /preloadfmt;

var weight;

format age test_fmt.;

run;

Re: Proc freq using SPARSE

Paul_NYS — Wed, 04 Dec 2013 21:57:22 GMT

Thanks Reeza. I will try this and re-try the classdata method. It does make sense to create the combinations that do not exist in the data set.

Paul