Show categories with no data

[Ronein]

Hello

I am trying to show in summary report also categories that don't appear in raw data set.

What is the problem in my code that I get null values???

Data have;
input VAR $12.   VarIssue $ 14-17   category  $ 19-28  nr pct;
cards;
cat_ageYears Age (a) Null    100 10
cat_ageYears Age (c) 26-31   300 30
cat_ageYears Age (d) 32-42   200 20
cat_ageYears Age (e) 43-67   100 10
cat_ageYears Age (f) more 67 300 30
;
Run;

proc format;
value $AgeFmt (multilabel notsorted)
Other ='(a) Null'
'less 25'='(b) less 25'
'26-31'='(c) 26-31'
'32-42'='(d) 32-42'
'43-67'='(e) 43-67'
'more 67'='(f) more 67'
'All Other'='(g) All Other'
;
Run;


proc summary data =have nway completetypes;
class VAR;
Class VarIssue;
class Category / preloadfmt mlf order=formatted;
var nr pct;
output out = want(drop = _:) sum(nr)=nr sum(pct)=pct ;
format Category $AgeFmt.;
run;
/****problem- Rows get null value!!!!****/

[PaigeMiller · Accepted solution]

I don't see any "null values" when I run your code. Show us.

Also, your format $agefmt. is applied to variable category, and none of the values in category actually are assigned a format, can you see why?

Lastly, why not leave value of AGE as a number instead of a text string, and then assign formats to those numbers? This make a lot more sense than the text strings for category that you are using.

[Ronein]

Thanks,

This is the result of "want" data set 

As you can see except of first row all other rows get null values on fields nr and pct.

[Ronein]

Data have;
input VAR $12.   VarIssue $ 14-17   category  $ 19-28  nr pct;
cards;
cat_ageYears Age (a) Null    100 10
cat_ageYears Age (c) 26-31   300 30
cat_ageYears Age (d) 32-42   200 20
cat_ageYears Age (e) 43-67   100 10
cat_ageYears Age (f) more 67 300 30
;
Run;
proc format;
value $AgeFmt (multilabel notsorted)
Other ='(a) Null'
'26-31'='(c) 26-31'
'32-42'='(d) 32-42'
'43-67'='(e) 43-67'
'more 67'='(f) more 67'
/**The categories that need to add to summary table**/
'less 25'='(b) less 25'
'All Other'='(g) All Other'
;
Run;

proc summary data =have nway completetypes;
class VAR;
Class VarIssue;
class Category / preloadfmt mlf order=formatted;
var nr pct;
output out = want(drop = _:) sum(nr)=nr sum(pct)=pct ;
format Category $AgeFmt.;
run;

[Ronein]

Hello, This is the summary table that was created by others and the task is to add missing categories.

[PaigeMiller]

My apologies, when I ran the code myself, the missing values were there, and I didn't look at the correct data set.

Your formats do not apply to the data. I am asking you to look carefully at the formats and see this yourself. One of your format values is the text string '26-31', but this text string '26-31' does not appear in your original data set. Your formats won't work. You need to have formats that actually appear in the data.

[Ronein]

Thanks,

I have corrected the  values in  have data set,

Still get null values in want data set, why?

Data have;
input VAR $12.   VarIssue $ 14-16   category  $ 18-28  nr pct;
cards;
cat_ageYears Age (a) Null    100 10
cat_ageYears Age (c) 26-31   300 30
cat_ageYears Age (d) 32-42   200 20
cat_ageYears Age (e) 43-67   100 10
cat_ageYears Age (f) more 67 300 30
;
Run;
proc format;
value $AgeFmt (multilabel notsorted)
Other ='(a) Null'
'26-31'='(c) 26-31'
'32-42'='(d) 32-42'
'43-67'='(e) 43-67'
'more 67'='(f) more 67'
/**The categories that need to add to summary table**/
'less 25'='(b) less 25'
'All Other'='(g) All Other'
;
Run;
proc summary data =have nway completetypes;
class VAR;
Class VarIssue;
class Category / preloadfmt mlf order=formatted;
var nr pct;
output out = want(drop = _:) sum(nr)=nr sum(pct)=pct ;
format Category $AgeFmt.;
run;

[PaigeMiller]

Let's take a simple example.

proc format;
value $sexf 'M'='Male';
run;
proc print data=sashelp.class;
format sex $sexf.;
run;

What does this do? It finds every case where the variable SEX has value 'M' and it makes the 'M' appear as the text string 'Male'.

In your case, your format is trying to find the text string '26-31' and make it appear as '(c) 26-31'. However, the text string '26-31' does not appear in your data set. Please look at your data set and see that the value of variable CATEGORY is never '26-31'. (If you do see it, then show me)

Your formats are wrong. They do not work. You need to fix them.

[Ronein]

Thank you again, I will fix it and will run the code tomorrow's morning and will verify that at it will work, cheers

[Ronein]

Hello, I have fixed the proc format but still get an error

ERROR: A format range for the variable category could not be pre-loaded.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 5 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.

Data have;
input VAR $12.   VarIssue $ 14-17   category  $ 19-28  nr pct;
cards;
cat_ageYears Age (a) Null    100 10
cat_ageYears Age (c) 26-31   300 30
cat_ageYears Age (d) 32-42   200 20
cat_ageYears Age (e) 43-67   100 10
cat_ageYears Age (f) more 67 300 30
;
Run;
proc format;
value $AgeFmt (multilabel notsorted)
'(a) Null'='(a) Null'
'(b) less 25'='(b) less 25'
'(c) 26-31'='(c) 26-31'
'(d) 32-42'='(d) 32-42'
'(e) 43-67'='(e) 43-67'
'(f) more 67'='(f) more 67'
'(g) All Other'='(g) All Other'
;
Run;
proc summary data =have nway completetypes;
class VAR;
Class VarIssue;
class Category / preloadfmt mlf order=data;
var nr pct;
output out = want(drop = _:) sum(nr)=nr sum(pct)=pct ;
format Category $AgeFmt.;
run;