BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jonthiele
Fluorite | Level 6

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 ;
1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

View solution in original post

6 REPLIES 6
ed_sas_member
Meteorite | Level 14

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
Fluorite | Level 6

 

Thank you for this quick reply and solution!!!

yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



jonthiele
Fluorite | Level 6

 

This looks good as well.   Thank you. 

Aku
Obsidian | Level 7 Aku
Obsidian | Level 7

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
Super User Tom
Super User

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6805 views
  • 1 like
  • 5 in conversation