Summary output with all where variables

[brp4h]

Is it possible to force proc summary to include all possible combinations of variables in the where statement, even when those combinations don't appear in the dataset? I know that you can do this for the class statement. Maybe in conjunction with proc report?

Thanks!

[sbb]

Consider sharing sample INPUT and (desired) OUTPUT data represented - consider the WHERE statement is an input filter and the WHERE= (the dataset option) is an output-side filter. You're asking that combinations not meeting the WHERE statement filter criteria be included in the SUMMARY execution output -- plz help to explain this opportunity with your data-sample as an example of what you want to see.

Scott Barry
SBBWorks, Inc.

[brp4h]

Hi, Scott,

Here is an example:

year channel type numloans
1 A 1 100
2 B 1 5
3 C 1 23
4 A 2 7
5 B 2 10

proc summary data=loan_all missing completetypes;
class year channel /preloadfmt exclusive;
var numloans;
output out=test sum=;
format channel $channellbl.;
where type in ('1','2','3');
run;

My output will have every permutation of year and channel for types 1 and 2, but not for 3 because type is not in the class statement and never appears in the data. How can I print 0 for numloans for every permutation of year and channel for type 3? I cannot create a temporary dataset with all 0s because the variables will change each time the program is run.

[art297]

I'm not sure I understand what you are looking for. The following is based on the understanding that the data desribes the years and channels, but a where statement is the only thing that describes the desired 'types' you want to include.

If that is correct, then I'd suggest including the where statement as a macro variable and using it to build a file that contains all of the possible permutations.

For example:

proc format;
value $ channellbl
'A'='First'
'B'='Second'
'C'='Third';
run;

data loan_all;
input year (channel type) ($1.) numloans;
cards;
1 A1 100
2 B1 5
3 C1 23
4 A2 7
5 B2 10
;

%let where='1','2','3';

proc sort data=loan_all (keep=year) nodupkey out=allcombos1;
by year;
run;

proc sort data=loan_all (keep=channel) nodupkey out=allcombos2;
by channel;
run;

data allcombos3 (drop=_:);
length type $1;
_want=compress("&where.","'");
_i=1;
do while (scan(_want,_i) NE "");
type=put(scan(_want,_i),$1.);
_i+1;
output;
end;
run;

proc sql;
create table selectlevels as
select *
from Allcombos1,Allcombos2,Allcombos3
;
quit;

proc summary data=loan_all completetypes missing nway
classdata=selectlevels;
class year channel type;

var numloans;
output out=test sum=;
format channel $channellbl.;
where type in (&where.);
run;

data test;
set test;
if missing(numloans) the numloans=0;
run;

proc print data=test;
run;

The code, of course, could be reconfigured to be a SAS macro, in itself, but I didn't think it was worth the effort until we find out that it is close to what you were seeking.

Art