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

Discussion in 'SAS (Statistical Analysis Software)' started by DH, Feb 5, 2007.

  1. DH

    DH Guest

    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
     
    DH, Feb 5, 2007
    #1
    1. Advertisements

  2. DH

    DH Guest

    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
     
    DH, Feb 5, 2007
    #2
    1. Advertisements

  3. 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
    ;
     
    Richard A. DeVenezia, Feb 14, 2007
    #3
  4. DH

    DH Guest

    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
     
    DH, Feb 14, 2007
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.