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

I'm joining two tables. Within my joined table, a particular field has a null value that I'd like to replace with a non-null value. I'm wondering how I can accomplish this within one sql procedure. 

 

In the below reprex, I create a table that has a null value for id: 3 in the sales field. I'm trying to replace this null value with something like calls*0.1. The only way I know how to do this would be with a case statement in a separate sql procedure ... wondering how I can do this all in one procedure. Thanks!

 

/*create sample table a*/
data table_a; input id region $ calls; cards; 1 south 50 2 north 30 3 west 30 4 south 50 ; run;
/*create sample table b*/ data table_b; input id sales; cards; 1 5 3 4 4 6 ; run;
/*join tables a & b*/ proc sql; create table reprex as select * from table_a as a left join table_b as b on a.id = b.id; quit;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Just do not use the * short cut.  Instead actually list the variables you want to select.

create table reprex as
select a.id
     , a.region
     , a.calls
    , coalesce(b.sales,a.calls*0.10) as sales 
from table_a as a 
left join table_b as b
  on a.id = b.id
;

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

Just do not use the * short cut.  Instead actually list the variables you want to select.

create table reprex as
select a.id
     , a.region
     , a.calls
    , coalesce(b.sales,a.calls*0.10) as sales 
from table_a as a 
left join table_b as b
  on a.id = b.id
;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1086 views
  • 1 like
  • 2 in conversation