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
@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.
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
Thanks for the useful thread
A
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.