topic Re: How do I include 0 counts for possible values that aren't in the data set? in SAS Programming

How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Wed, 28 Dec 2022 17:54:34 GMT

Hi,

This is a data manipulation exercise. How do I include 0 counts for possible values that aren't in the data set?

The possible values are the colors of the rainbow, ROYGBIV. (red orange yellow green blue indigo violet). Currently indigo is missing.

Thanks

data have;
infile datalines dsd dlm=",";
	input color $;
datalines;
blue
red
red
blue 
green
red
orange
red
blue
violet
red
orange
yellow
;
run;

Desired output: (sort order doesn't matter)

Re: How do I include 0 counts for possible values that aren't in the data set?

fja — Wed, 28 Dec 2022 19:11:40 GMT

Would you please have a look at this posting: https://communities.sas.com/t5/SAS-Programming/Return-count-of-0-in-a-Group-By-SQL-Statement/m-p/539625
This should be applicable in your case too ...

Re: How do I include 0 counts for possible values that aren't in the data set?

PeterClemmensen — Wed, 28 Dec 2022 19:19:54 GMT

Try this

data have;
infile datalines dsd dlm=",";
input color $;
datalines;
blue   
red    
red    
blue   
green  
red    
orange 
red    
blue   
violet 
red    
orange 
yellow 
;

proc format;
 value $ col   "red"     = "red" 
               "orange"  = "orange" 
               "yellow"  = "yellow" 
               "green"   = "green" 
               "blue"    = "blue" 
               "indigo"  = "indigo" 
               "violet"  = "violet" 
;
run;

proc summary data = have nway completetypes;
   class color / preloadfmt order = formated missing;
   format color $col.;
   output out = want(drop = _TYPE_ rename = _FREQ_ = count);
run;

Re: How do I include 0 counts for possible values that aren't in the data set?

andreas_lds — Wed, 28 Dec 2022 20:35:59 GMT

Using a format, as shown by @PeterClemmensen , is the recommended way, because just one more step is required.

Re: How do I include 0 counts for possible values that aren't in the data set?

FreelanceReinh — Wed, 28 Dec 2022 21:47:13 GMT

With PROC SUMMARY and PROC MEANS you can also use a CLASSDATA= dataset which contains (at least) the missing categories.

Simplified example (creating only printed output with a different header of the count column) using PROC MEANS:

data cd;
color='indigo  ';
run;

proc means data=have classdata=cd;
class color;
run;

(Note the two trailing blanks in 'indigo ' to make variable COLOR the same length as in dataset HAVE.)

Re: How do I include 0 counts for possible values that aren't in the data set?

fja — Wed, 28 Dec 2022 21:53:33 GMT

@andreas_lds wrote:

Using a format, as shown by @PeterClemmensen , is the recommended way, because just one more step is required.

I really do appreciate the two of you sharing your expertise here. ... but isn't the solution using a proc format not more like using a side effect? Isn't there any "official" way of addressing this issue?

Re: How do I include 0 counts for possible values that aren't in the data set?

PaigeMiller — Wed, 28 Dec 2022 22:09:50 GMT

In SAS, there are often many ways to get to the desired result. None of them are "official". One may be easier than another; one may require less code than another; one may execute faster than another.

Re: How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Wed, 28 Dec 2022 22:34:50 GMT

Thanks for replying, but this solution is slightly different because my data set does not include the possible value that I need.

This solution in the posted link appears to dynamically code the solution based on creating possible combinations from all distinct cross tabulated values of the two groups in the data set.

Re: How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Wed, 28 Dec 2022 23:22:13 GMT

Thanks, FreelanceReinhard!

This is a very clean way of doing it.

Edit: this post was edited to remove some follow up questions that i had.

Re: How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Wed, 28 Dec 2022 22:46:46 GMT

Thanks!

Re: How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Wed, 28 Dec 2022 23:20:42 GMT

I edited this post, but originally I had questions about how i would make this method more robust. Especially in particular cases where the data set was updated and the counts changed and if there was a way to macrotize the length value of the data set so i can use it for the cd data set. But after thinking about it some more, my use case involves knowing what the categories are beforehand and I would already know what the maximum length extends out to so it would be ok to hardcode that in the data set cd.

Re: How do I include 0 counts for possible values that aren't in the data set?

fja — Thu, 29 Dec 2022 09:06:29 GMT

@PaigeMiller wrote:

In SAS, there are often many ways to get to the desired result. None of them are "official". One may be easier than another; one may require less code than another; one may execute faster than another.

That is understandable ... could you name some advantages of the solution using "proc format"? This time just to help me ... 🙂 (as there is an accepted solution already).

Re: How do I include 0 counts for possible values that aren't in the data set?

FreelanceReinh — Thu, 29 Dec 2022 13:18:35 GMT

@Hello_there wrote:
I edited this post, but originally I had questions about how i would make this method more robust. Especially in particular cases where the data set was updated and the counts changed and if there was a way to macrotize the length value of the data set so i can use it for the cd data set. But after thinking about it some more, my use case involves knowing what the categories are beforehand and I would already know what the maximum length extends out to so it would be ok to hardcode that in the data set cd.

As long as the CD dataset contains all CLASS variable values that would be absent otherwise, the approach should work regardless of changed counts in dataset HAVE. It wouldn't hurt if CD redundantly contained values which are present in HAVE as well.

There is no need to hardcode the length of variable COLOR in dataset CD as you can always retrieve it from dataset HAVE. (Edit: ... assuming that the length there is also sufficient to accommodate the new values in CD.)

Example:

data cd;
if 0 then set have(keep=color);
input color;
cards;
indigo
;

Re: How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Thu, 29 Dec 2022 13:24:42 GMT

Thanks, again.

Could you explain what is 0 in the "if 0 then set have (keep=color);" part?

Re: How do I include 0 counts for possible values that aren't in the data set?

FreelanceReinh — Thu, 29 Dec 2022 14:01:01 GMT

@Hello_there wrote:
Could you explain what is 0 in the "if 0 then set have (keep=color);" part?

The numeric value 0 as an IF condition is interpreted as the Boolean value FALSE. Hence, what follows after THEN (here: the SET statement) is never executed. Yet, the SET statement and also the KEEP= dataset option have an impact during the compilation of the DATA step: Variable COLOR (and no other variable from dataset HAVE) enters the program data vector (PDV) retrieving the variable type (character) and length (8 bytes) from dataset HAVE. Thus it is already available when the INPUT statement is reached. As a consequence, we neither need to specify the type (e.g., the dollar sign in "input color $"), nor the length of COLOR explicitly in this DATA step.

Re: How do I include 0 counts for possible values that aren't in the data set?

Hello_there — Thu, 29 Dec 2022 14:03:19 GMT

Thanks for the thorough explanation!

I appreciate the time you take to expound on these concepts.

Re: How do I include 0 counts for possible values that aren't in the data set?

FreelanceReinh — Fri, 30 Dec 2022 15:56:11 GMT

@fja wrote:
... could you name some advantages of the solution using "proc format"?

I think in many practical use cases, there is a suitable format already available and associated with the categorical variable in question. In this situation, neither the PROC FORMAT step nor the FORMAT statement as shown in PeterClemmensen's solution would be needed. (The existence of a suitable dataset for the CLASSDATA= option is probably less common.) Typically, this format would map numeric or short character codes to longer text descriptions of the categories.

A special feature of the PRELOADFMT approach is available when used in conjunction with the ORDER=DATA option of the CLASS statement (of PROC MEANS, PROC SUMMARY or PROC TABULATE): If the format definition used the NOTSORTED option of PROC FORMAT's VALUE statement, the order of categories in the output will match the order from the format definition -- regardless of the (alphabetic) order of the formatted values, the default order of the unformatted values, the order in which they occur in the input dataset (!) and their frequencies. This is very useful if the PROC FORMAT code was written in view of the output specifications (e.g., table shells in a statistical analysis plan).