Hello,
I am trying to update a master table with 15,401 observations of rural-urban continuum codes where there are 651 observations are labelled as missing because they are redacted. Using proc update, I am trying to update those redacted values using another table with just those 651 redacted values that have been un-redacted via each observations sequence number. My code does update the table, but it makes the 14750 observations that were not redacted into missing values.
PROC SQL;
UPDATE major as m
SET RUCC_2013 = (select RUCC_2013 from minor where sequence_number = m.SEQNO);
quit;
Thank you for your solutions,
Donald S.
You have not put any where clause on the update that is why it updates all records:
PROC SQL;
UPDATE major as m
SET RUCC_2013 = (select RUCC_2013 from minor where sequence_number = m.SEQNO)
where <conditon>; /* The condition here being which rows you want to update */
quit;
You have not put any where clause on the update that is why it updates all records:
PROC SQL;
UPDATE major as m
SET RUCC_2013 = (select RUCC_2013 from minor where sequence_number = m.SEQNO)
where <conditon>; /* The condition here being which rows you want to update */
quit;
Hello RW9, that worked great. Here is what I did encase someone else gets stuck:
First I re-wrote the code with the new where clause:
PROC SQL;
UPDATE TEMP4 as t
SET RUCC_2013 = (select RUCC_2013 from minnesota where sequence_number = t.SEQNO)
where RUCC_2013 = '.';
quit;
I received the error "ERROR: Expression using equals (=) has components that are of different data types." though both the sequence number variables and the RUCC_2013 variable in both datasets were labelled numerical. I changed the missing values to a numerical value instead of being left blank:
data temp4;
set temp3;
if RUCC_2013 = '.' then RUCC_2013 = 11;
run;
Re-ran the code:
PROC SQL;
UPDATE TEMP4 as t
SET RUCC_2013 = (select RUCC_2013 from minnesota where sequence_number = t.SEQNO)
where RUCC_2013 = 11;
quit;
And it worked perfectly.
Though this worked, if you think there is a better way to do it, I would very much enjoy learning that as well!
Thank you!
Yes, the reason you have got that is due to the fact you are comparing apples and oranges:
where RUCC_2013 = '.';
In this section you are comparing RUCC_2013 which is obviously a numeric field (from your later code) with the text string '.' Your later code you fixed this:
where RUCC_2013 = 11;
So comparing the number 11 with the numeric RUCC_2013.
If you change your first code to:
where RUCC_2013 = .;
or even:
where RUCC_2013 is null;
Then you will get the correct response.
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.