Help using Base SAS procedures

Summary output with all where variables

Reply
New Contributor
Posts: 2

Summary output with all where variables

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!
Super Contributor
Super Contributor
Posts: 3,174

Re: Summary output with all where variables

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.
New Contributor
Posts: 2

Re: Summary output with all where variables

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.
PROC Star
Posts: 7,480

Re: Summary output with all where variables

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=_Smiley Happy;
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
Ask a Question
Discussion stats
  • 3 replies
  • 114 views
  • 0 likes
  • 3 in conversation