SQL Left Join Warning - Join 3 tables

Discussion in 'SAS (Statistical Analysis Software)' started by Warren Schlechte, Nov 5, 2008.

  1. Here is the log from some sql steps I have coded.

    I am selecting specific elements from specific rows from a dataset named
    ld50_est, and then join the data I want into a new table based on the
    variable n_time.

    The code works as I want, but I get a warning.

    I have tried several things, but cannot seem to get clean running code
    without the warning.

    I would appreciate your help.

    31 proc sql;
    32 create table tmp1 (drop=_type_)as
    33 select n_time, intercept, trtmnt, ld50, _type_
    34 from ld50_est
    35 where _type_="PARMS";
    NOTE: Table WORK.TMP1 created, with 4 rows and 4 columns.

    37 create table tmp2 as
    38 select n_time, var_b0
    39 from ld50_est
    40 where var_b0 ^=.;
    NOTE: Table WORK.TMP2 created, with 4 rows and 2 columns.

    42 create table tmp3 as
    43 select n_time, var_b1, cov_b0b1
    44 from ld50_est
    45 where var_b1 ^=.;
    NOTE: Table WORK.TMP3 created, with 4 rows and 3 columns.

    47 create table ld50_parms as
    48 select *
    49 from (tmp1
    50 left join tmp2
    51 on tmp1.n_time=tmp2.n_time)
    52 left join tmp3
    53 on tmp2.n_time=tmp3.n_time
    54 ;
    WARNING: Variable N_Time already exists on file WORK.LD50_PARMS.
    WARNING: Variable N_Time already exists on file WORK.LD50_PARMS.

    Warren Schlechte
    HOH Fisheries Science Center
    5103 Junction Hwy
    Mt. Home, TX 78058
    Phone 830.866.3356 x214
    Fax 830.866.3549
    Warren Schlechte, Nov 5, 2008
    1. Advertisements

  2. Warren Schlechte

    bigD Guest

    You have the same variable name in two different data sets. Sas will
    drop the values from the data set on the "right" in this case, and
    only keep the variables on the left. Usually not a problem, unless
    variables from the two datasets contain different information.
    bigD, Nov 5, 2008
    1. Advertisements

  3. Warren Schlechte

    Akshaya Guest

    This Warning message occurs, when same named variables exist in all
    the tables that are joined in SQL, when creating table from the query.
    It doesn't occur, when CREATE TABLE clause is not used, i.e., just for
    printing records. Solution is, if variables from both or all tables
    are same data type and values, qualify the variable name only from
    the table which you want to keep, and don't select the same variable
    from other tables.
    Akshaya, Nov 5, 2008
    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.