Hi All

Is anyone aware of a good technique to stop the following message from appearing when joining tables using PROC SQL?

"WARNING: Variable XXXX already exists on file WORK.XX"

In my example below I want to select all exclusive fields in both tables A and B as well as the fields used to join (I don't care which table the join fields come from as it makes no difference).

proc sql noprint;
  create table growth_rate as
  select *
  from smry_n2   a
  join smry_sum2 b  on b._name_ eq a._name_
                   and b.group  eq a.group 
                   and b.bucket eq a. bucket
                   and b._type_ eq a._type_
  ;
quit;

UPDATE: I don't want to have to type them all out explicitly because I have a large number of fields in each table.

Thanks Rob

asked 20 Sep '10, 22:42

Robert%20Penridge's gravatar image

Robert Penridge ♦
911724
accept rate: 36%

edited 22 Sep '10, 20:18


If for some reaons you can't (or don't want to) hard code all the variable names, you can use a couple of dictionary queries to build a warning-free select statement in macro, as per the following example:

/*Arbitrary test data table 1:*/
data abcd;  length a 8 b 8 c 8 d 8; do a = 1 to 10; b = a; c = a ; d = a;     output; end; run;

/*Arbitrary test data table 2:*/
data cdef; length c 8 d 8 e 8 f 8; do c = 5 to 15; d = c; e = d ; f = d; output; end; run;

/* Load the names of all columns on table A into a macro variable ... */
proc sql noprint;
        select  cats("t1.",name) 
        into    :t1_cols separated by ','
        from    dictionary.columns
        where   libname = 'WORK'
            and memname = upcase('ABCD')
    ;
quit;
%put t1_cols: &t1_cols.;

/* Load the names of all columns on table B which aren't on table A into another macro     variable ... */
proc sql noprint;
    select  cats("t2.",name)
    into    :t2_cols separated by ','
    from    dictionary.columns
    where   libname = 'WORK'
        and memname = upcase('CDEF')
        and name not in (
            select  name
            from    dictionary.columns
            where   libname = 'WORK'
                and memname = upcase('ABCD')
        )
    ;
quit;
%put t2_cols: &t2_cols.;

/* Use macro variables created above in the select clause ...*/
proc sql;
    create table abcdef as
        select  &t1_cols.
                ,&t2_cols.
        from    abcd as t1
            FULL JOIN cdef as t2
                on t1.c = t2.c
                AND t1.d = t2.d
    ;
quit;

Make sure the table aliases you use are the same as the prefixes you use in the "cats" function!

This didn't come out as neatly as I'd have liked it to, can anybody figure out a way to make it work with a single (and not horribly complex) dictionary query?

-- Regards, Will

link

answered 22 Sep '10, 15:32

WilliamDobson's gravatar image

WilliamDobson
567214
accept rate: 36%

PROC SQL statement option NOWARN.

1087  proc sql;
1088     create table class as
1089     select *
1090     from sashelp.class as a
1091     join sashelp.class as b on a.name eq b.name;
WARNING: Variable Name already exists on file WORK.CLASS.
WARNING: Variable Sex already exists on file WORK.CLASS.
WARNING: Variable Age already exists on file WORK.CLASS.
WARNING: Variable Height already exists on file WORK.CLASS.
WARNING: Variable Weight already exists on file WORK.CLASS.
NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

1092     quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      cpu time            0.04 seconds


1093     run;

1094  proc sql nowarn;
1095     create table class as
1096     select *
1097     from sashelp.class as a
1098     join sashelp.class as b on a.name eq b.name;
NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

1099     quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


1100     run;
link

answered 24 Sep '10, 19:59

John%201's gravatar image

John 1
28613
accept rate: 22%

John - I like your solution but I've opted with the other as the poster pointed out - the scope of the NOWARN on proc sql is unclear.

(16 Mar '11, 21:42) Robert Penridge ♦

Not knowing the scope of this NOWARN option, I rather use the official SAS solution:

proc sql; create table all(drop=tmpid) as select * from one, two(rename=(id=tmpid)) where one.id = two.tmpid; quit;

link

answered 03 Nov '10, 15:50

haikuo's gravatar image

haikuo
261
accept rate: 100%

Spell out your select statement in more detail, and only select the overlapping variable(s) from one table:

proc sql noprint;
  create table growth_rate as
  select a.*,
         b.some_var_only_in_b_1,
         b.some_var_only_in_b_2,
         b.some_var_only_in_b_3
  from smry_n2   a
  join smry_sum2 b  on b._name_ eq a._name_
                   and b.group  eq a.group 
                   and b.bucket eq a. bucket
                   and b._type_ eq a._type_
  ;
quit;
link

answered 22 Sep '10, 10:51

Martin%20B%C3%B8gelund's gravatar image

Martin Bøgelund ♦
1195
accept rate: 28%

Hi Martin- while your answer is correct it doesn't address the issue I have which is that I don't want to have to type them all out explicitly because I have a large number of fields in each table. I've updated the question to reflect this. Thanks for the response though.

(22 Sep '10, 20:17) Robert Penridge ♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×19
×2

Asked: 20 Sep '10, 22:42

Seen: 4,104 times

Last updated: 03 Nov '10, 15:50

powered by OSQA