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 .
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;
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?
No, it doesn't show up in this specific data set actually.
Paul
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.
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
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;
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.
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.