Help using Base SAS procedures

Proc freq using SPARSE

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Proc freq using SPARSE

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    .


Accepted Solutions
Solution
‎12-04-2013 12:53 PM
Super User
Posts: 19,869

Re: Proc freq using SPARSE

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;

View solution in original post


All Replies
Super User
Posts: 19,869

Re: Proc freq using SPARSE

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?

Regular Contributor
Posts: 216

Re: Proc freq using SPARSE

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

Paul

Respected Advisor
Posts: 3,799

Re: Proc freq using SPARSE

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.

Regular Contributor
Posts: 216

Re: Proc freq using SPARSE

Posted in reply to data_null__

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

Regular Contributor
Posts: 216

Re: Proc freq using SPARSE

Posted in reply to data_null__

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;

Super User
Posts: 11,343

Re: Proc freq using SPARSE

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.

Solution
‎12-04-2013 12:53 PM
Super User
Posts: 19,869

Re: Proc freq using SPARSE

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;

Regular Contributor
Posts: 216

Re: Proc freq using SPARSE

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 980 views
  • 0 likes
  • 4 in conversation