Newbie question, Proc SQL to be used more than likely.

Hello all,

Here is what I want to do, or something close to it.

I have a table with 183 columns, variables (original table).

I would like to replace one column that contains IDNUM $9, in the
original table; with an IDNUM $10 from a column in another table and
add the corresponding NameLegal variable after the updated IDNUM so
that users can spot check the new IDNUM with the Name2 vs Name1.

The other table has both SSN $10, IDNUM $10, and Name2 $30.

I have included some data steps so that we are on the same page.

data origTable;
input IDNUM $ 1-9 /* Actually SSN */
Name1 $ 10-26
City $ 28-34
State $ 36-37;
cards;
001234567John Q. Public Anycity MT
002345678Jane Z. Public Anycity ID
003456789Fred D. Flintstone Bedrock AR
;
run;

data newdata;
input
IDNUM $ 1-10
SSN $ 11-20
Name2 $ 21-50;
cards;
999001 001234567Johnny Q. Public
999032 002345678Jane Z. Public
999904 003456789Barney Rubble
;
run;

The resulting origTable would look like the following:
" 999001","Johnny Q. Public","John Q. Public","Anycity","MT"
" 999032","Jane Z. Public","Jane Z. Public","Anycity","ID"
" 999904","Barney Rubble","Fred D. Flintstone","Bedrock","AR"

Showing possible mismatch in observation 1 and definite mismatch in
observation 3.

TIA

Richard

 

Sorry, without a direct question; I'm sure many of you were wondering
"what?"

I'm not worried about the mismatches; they were just there to
illustrate potential outcomes of the process.

My direct question would be, 'How would I create the "new" "original"
table; or updating the existing "original" table?'

Thanks,

Richard

 

You might want to use a VIEW. Is there a particular reason you would want
to actually alter origTable ?

--untested--

Proc SQL;
create view MyCheckItOutView as
select new.idnum, orig.name1, new.name2, orig.city, orig.state
from
origdata as orig
left join
newdata as new
on
orig.idnum = new.ssn
;

 

HI Richard, Thanks for the response.

I need to eliminate SSN from stored data and replace it with a unique
ID. I did come up with a solution though and now have a new post
requesting assistance with windows Batch files and SAS command line if
possible:

http://groups.google.com/group/comp...dc80b8f053e/5074caa0103d2312#5074caa0103d2312