Help using Base SAS procedures

Proc Update: Updating SAS master table with another table creates missing values

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

Proc Update: Updating SAS master table with another table creates missing values

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.


Accepted Solutions
Solution
‎07-08-2015 11:20 AM
Super User
Super User
Posts: 7,565

Re: Proc Update: Updating SAS master table with another table creates missing values

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;

View solution in original post


All Replies
Solution
‎07-08-2015 11:20 AM
Super User
Super User
Posts: 7,565

Re: Proc Update: Updating SAS master table with another table creates missing values

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;

Contributor
Posts: 74

Re: Proc Update: Updating SAS master table with another table creates missing values

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!

Super User
Super User
Posts: 7,565

Re: Proc Update: Updating SAS master table with another table creates missing values

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 272 views
  • 3 likes
  • 2 in conversation