topic Re: Make PROC SQL display rows with missing values in SAS Programming

Make PROC SQL display rows with missing values

tampham92 — Thu, 02 Sep 2021 17:26:43 GMT

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;

Re: Make PROC SQL display rows with missing values

ballardw — Thu, 02 Sep 2021 17:42:05 GMT

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.

Re: Make PROC SQL display rows with missing values

Reeza — Thu, 02 Sep 2021 19:18:58 GMT

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.

Re: Make PROC SQL display rows with missing values

PGStats — Thu, 02 Sep 2021 20:25:33 GMT

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)