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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.