BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Paul_NYS
Obsidian | Level 7

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    .

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Reeza
Super User

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?

Paul_NYS
Obsidian | Level 7

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

Paul

data_null__
Jade | Level 19

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.

Paul_NYS
Obsidian | Level 7

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

Paul_NYS
Obsidian | Level 7

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;

ballardw
Super User

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.

Reeza
Super User

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;

Paul_NYS
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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