Returning a result of '0' for a combination of variables where no instances of the combination exist

[AJChamberlain]

Hi everyone

I have a dataset in SAS with variables Var1 Var2 and Var3, Var1 is A or B Var2 is X or Y and for each combination of these I count the number of instances of Var 3. So this dataset:

Var1	Var2	Var3
A	X	1
A	Y	1
A	X	1
A	X	1
B	X	1
B	X	1
B	Y	1
B	Y	1

Produces this summary:

                    X                   Y

A	3	1
B	2	2

but when there are no instances of some combinations I still want to report a null result as zero for all combinations, so for example this dataset:

Var1	Var2	Var3
A	X	1
A	Y	1
A	X	1
A	X	1
A	X	1
A	X	1
A	Y	1
A	Y	1

produces this:

                     X                Y

A

5

3

where I want it to produce this:

	X	Y
A	5	3
B	0	0

I'm using proc SQL to count my instances, but I need some way of telling the system to look out for all combinations of Var1 and Var2 and return 0 where they don't exist. Can anyone tell me how I might do this?

Any ideas gratefully received!

Thanks

Andy

[PeterClemmensen]

Is your data representable of your actual problem? I.e, do you only have the two categories A and B to worry about, or do you have more?

Perhaps the approaches in this thread may be of help

https://communities.sas.com/t5/SAS-Procedures/PROC-FREQ-Include-Zero-Counts/td-p/325505

[AJChamberlain]

Thanks for the useful thread

A

[ballardw · Accepted solution]

---

@AJChamberlain wrote:

Hi everyone

but when there are no instances of some combinations I still want to report a null result as zero for all combinations, so for example this dataset:

Are you looking for a dataset (used by other parts of a program0 or a report (people read these)?

Do you know all the values that Var1 and Var2 will have? Then perhaps a format for the var1 and the option Preloadfmt

proc format library=work;
value $v
'A'='A'
'B'='B'
;
run;
data have;
input Var1 $ Var2 $ Var3 ;
datalines;
A X 1 
A Y 1 
A X 1 
A X 1 
A X 1 
A X 1 
A Y 1 
A Y 1 
;
run;

proc tabulate data=have;
   class var1 /preloadfmt;
   format var1 $v.;
   class var2;
   var var3;
   table var1,
         var2*var3=''*n=''
         /printmiss
   ;
run;
   

Proc means/summary will do similar for a data set though you need to add options completetypes and missing to the proc statement.