DATA Step, Macro, Functions and more

PROC SQL - UPDATE WITHOUT OVERWRITING WITH MISSING VALUES

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

PROC SQL - UPDATE WITHOUT OVERWRITING WITH MISSING VALUES

I am trying to update a master table with a reference table but my reference table isn't perfect and I don't want it to overwrite my master table if it's missing some of the devices I need.  

 

I was thinking  when I submit the following code it wouldn't update my speeds in my master table if they weren't in my reference table but it seems to still set my master table values to missing if they are not in the reference table. Am I missing something?

 

proc sql;

update mastertable tableA

set speed = (select Speed

from referencetable

where (tableA.device = device and tablea.daydate between min_daydate AND max_daydate));

where device in(select device from referencetable);

quit;


Accepted Solutions
Solution
‎06-09-2017 12:11 PM
New Contributor
Posts: 4

Re: PROC SQL - UPDATE WITHOUT OVERWRITING WITH MISSING VALUES

I figured it out!!!!

 

 

proc sql;

update mastertable tableA

set speed = case when device in (select device from referencetable) then (select Speed

from referencetable

where (tableA.device = device and tablea.daydate ge min_daydate and tablea.daydate lt max_daydate)) else speed end;

where device in (select device from referencetable);

quit;

View solution in original post


All Replies
Solution
‎06-09-2017 12:11 PM
New Contributor
Posts: 4

Re: PROC SQL - UPDATE WITHOUT OVERWRITING WITH MISSING VALUES

I figured it out!!!!

 

 

proc sql;

update mastertable tableA

set speed = case when device in (select device from referencetable) then (select Speed

from referencetable

where (tableA.device = device and tablea.daydate ge min_daydate and tablea.daydate lt max_daydate)) else speed end;

where device in (select device from referencetable);

quit;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 105 views
  • 0 likes
  • 1 in conversation