turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc Update: Updating SAS master table with anothe...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-08-2015 11:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-08-2015 11:20 AM

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;

All Replies

Solution

07-08-2015
11:20 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-08-2015 11:20 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-08-2015 12:22 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-08-2015 12:57 PM

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.