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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
kmcnulty
Obsidian | Level 7

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

1 REPLY 1
kmcnulty
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1 reply
  • 1560 views
  • 0 likes
  • 1 in conversation