topic Defaulting Missing Values in a PROC SQL Left Join in SAS Programming

Defaulting Missing Values in a PROC SQL Left Join

jonthiele — Fri, 21 Feb 2020 11:27:14 GMT

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 ;

Re: Defaulting Missing Values in a PROC SQL Left Join

ed_sas_member — Fri, 21 Feb 2020 11:54:09 GMT

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;

Re: Defaulting Missing Values in a PROC SQL Left Join

yabwon — Fri, 21 Feb 2020 11:51:26 GMT

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

Re: Defaulting Missing Values in a PROC SQL Left Join

jonthiele — Fri, 21 Feb 2020 12:14:29 GMT

Thank you for this quick reply and solution!!!

Re: Defaulting Missing Values in a PROC SQL Left Join

jonthiele — Fri, 21 Feb 2020 12:15:01 GMT

This looks good as well. Thank you.

Re: Defaulting Missing Values in a PROC SQL Left Join

Aku — Fri, 21 Feb 2020 13:44:07 GMT

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;

Re: Defaulting Missing Values in a PROC SQL Left Join

Tom — Fri, 21 Feb 2020 13:54:11 GMT

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.