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

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

daszlosek — Wed, 08 Jul 2015 15:16:46 GMT

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.

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

RW9 — Wed, 08 Jul 2015 15:20:47 GMT

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;

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

daszlosek — Wed, 08 Jul 2015 16:22:55 GMT

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!

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

RW9 — Wed, 08 Jul 2015 16:57:08 GMT

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.