Defaulting Missing Values in a PROC SQL Left Join

[jonthiele]

In a Left Join, I may have missing values that I would like to default to some specific string.  I can't seem to determine when the Right table is not contributing observations.  In the following code, I want a table that looks like:

k1 aa bb gg hh
k2 cc dd XX YY         <====   defaulted data since no k2 value in right table   
k3 ee ff ii jj

Reading posts from this community, I think I want to use the CASE WHEN DO END sequence of statements but I can't seem to code them correctly - or if my test of IS NULL is correct.

data file1;
input 	key1 $
	var1 $
	var2 $;
datalines;
k1 aa bb
k2 cc dd
k3 ee ff
;

data file2;
input 	key2 $
	var3 $
	var4 $;
datalines;
k1 gg hh
k3 ii jj
; 

proc sql ;
     create table file3 as
	select  a.key1,
		a.var1,
		a.var2,
		b.var3,
		b.var4	
	case when b.key2 is null then 
	do ;
	        b.var3 = 'XX' ;
		b.var4 = 'YY' ;
	end ; 
	from file1 as A left join
	     file2 as B
	on   a.key1 = b.key2 ; 

[ed_sas_member · Accepted solution]

Hi @jonthiele 

Does this code meet your expectations?

All the best,

proc sql ;
    create table file3 as
	select  a.key1,
		a.var1,
		a.var2,
		case when b.key2 is null then 'XX'
		     else b.var3
		     end as var3,
		case when b.key2 is null then 'YY'
		     else b.var4
		     end as var4
	from file1 as a left join
	     file2 as b
	on   a.key1 = b.key2 ; 
quit;

[jonthiele]

Thank you for this quick reply and solution!!!

[yabwon]

Hi,

Try this:

proc sql ;
     create table file3 as
	select  
    a.key1,
		a.var1,
		a.var2, 
    case when b.key2 is null then 'XX' else b.var3 end as va3,
    case when b.key2 is null then 'YY' else b.var4 end as va4


	from file1 as A left join
	     file2 as B
	on   a.key1 = b.key2 ; 

  quit;

All the best

Bart

[jonthiele]

This looks good as well.   Thank you. 

[Aku]

CoalesceC -function (for char values) is perfect for joins like this. Coalesce is for Num values.

proc sql;
create table want as
select f1.Key1,
F1.Var1,
F1.Var2,
Coalescec(F2.Var3,'XX') as Var3,
Coalescec(F2.Var4,'YY') as Var4
from File1 F1
left join File2 f2
on F1.Key1 = F2.Key2;
quit;

[Tom]

Note that in SQL code the SQL defined COALESCE() function is used.  So one function can be used for both numeric and character values.   It is just in non-SQL code that you need to use COALESCEC(). Although you can use the SAS specific COALESCEC() function if you want, just like you can use any other SAS function.