Make PROC SQL display rows with missing values

[tampham92]

I am having issues to make PROC SQL shows rows with missing values. For example, I have 8 note ranges and some note range has no values when I aggregate it but I want to display all 8 note ranges for each product even though it has no values. Thanks.

proc sql;
create table summary as
select
as_of_date
,Channel
,QRM_Prod
,Note_Range
,sum(before_upb) as before_upb
,sum(after_UPB) as after_UPB
,sum(before_pmt) as before_pmt
,sum(after_pmt) as after_pmt

from msr_act_202102_202107
group by as_of_date
,Channel
,QRM_Prod
,Note_Range
order by as_of_date
,Channel
,QRM_Prod
,Note_Range
;
quit;

[ballardw]

What is a "note  range"?

Please list the variable(s) by name that have missing values.

Best would be to provide a small example data set in the form of data step code that shows the behavior you describe and a second data set showing what you expect for the result. Then we can test with data whether a proposed solution creates the desired output.

[Reeza]

So you want an entry for NOTE_RANGE=8 say, when the dataset has no records with a value of NOTE_RANGE=8?
If there are no entries with NOTE_RANGE=8 how would SAS know it needs to exist? Or that you don't need NOTE_RANGE=9, 10, or 100?

You can create a master list and merge that with the data so you'll get missing values but you have to have a data set somewhere that has all the values.
If not using SQL, preloadformat and classdata are options to handle this using proc means or tabulate, which would work fine for this use case.

[PGStats]

Use a cross product to create all possible combinations:

proc sql;
create table summary as
select
	a.as_of_date
	,a.Channel
	,a.QRM_Prod
	,b.Note_Range
	,sum(c.before_upb) as before_upb
	,sum(c.after_UPB) as after_UPB
	,sum(c.before_pmt) as before_pmt
	,sum(c.after_pmt) as after_pmt

from 
	(select distinct
	 as_of_date
	 ,Channel
	 ,QRM_Prod
	 from msr_act_202102_202107) as a cross join
	 
	(select distinct  
	 Note_Range 
	 from msr_act_202102_202107) as b left join
	 
	msr_act_202102_202107 as c on 
		a.as_of_date=c.as_of_date and 
		a.Channel=c.Channel and 
		a.QRM_Prod=c.QRM_Prod and
		b.Note_Range=c.Note_Range

group by 
	a.as_of_date
	,a.Channel
	,a.QRM_Prod
	,b.Note_Range
;
quit;

(untested)