PROC SQL Warning Variable already exists

[JohnT]

Hi,

I'm using PROC SQL to merge some tables, and the joining variable name is common to both tables.

Here sample output of what I'm trying to do:

7
8 data a1;
9 set sashelp.adsmsg (keep = msgid MNEMONIC obs = 10);

NOTE: There were 10 observations read from the data set SASHELP.ADSMSG.
NOTE: The data set WORK.A1 has 10 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds


10 data a2;
11 set sashelp.adsmsg (keep = msgid TEXT obs = 10);
12 run;

NOTE: There were 10 observations read from the data set SASHELP.ADSMSG.
NOTE: The data set WORK.A2 has 10 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds


13
14 proc sql;
15 create table a_comb as
16 select *
17 from a1, a2
18 where a1.msgid = a2.msgid
19 ;
WARNING: Variable MSGID already exists on file WORK.A_COMB.
NOTE: Table WORK.A_COMB created, with 10 rows and 3 columns.

20 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
^^

How do I stop that WARNING message from showing up? Is there a system option or something similar? I don't want to explicitly list every variable, or use a RENAME statement on all my duplicate variable names, or run a PROC CONTENTS to derive the variables I want to keep.


Thanks,
-John T. Message was edited by: John T

[LinusH]

I'm not aware of such an options. Maybe you could make it a SASware ballot entry.
A little better than specifying all columns could to specify column for one table:

proc sql;
create table a_comb as
select a1.*, a2.text
from a1, a2
where a1.msgid = a2.msgid
;
quit;

Or you could build a macro that returns all columns (and nor doubling the join column).

/Linus

[JohnT]

Hi Linus,

Thanks for the prompt response !

I think my sample code was too simplistic. Your suggestion is better, however my queries would be a bit more complex than that, and have far more variables.

eg


606 proc sql;
607 create table AV_Ult as
608 select 'AV' as Entity, *
609 from summary.av_exp a
610 left join av_map d
611 on a.scheme_description=d.scheme_description
612 left join roc_param b
613 on b.company='AV'
614 and a.accyr=b.year
615 and a.cob=b.cob
616 left join newdiv_param e
617 on e.company='AV'
618 and a.accyr=e.year
619 and d.new_div=e.new_div
620 ;
WARNING: Variable scheme_description already exists on file WORK.AV_ULT.
WARNING: Variable COB already exists on file WORK.AV_ULT.
WARNING: Variable Company already exists on file WORK.AV_ULT.
WARNING: Variable Year already exists on file WORK.AV_ULT.
WARNING: Variable NEW_DIV already exists on file WORK.AV_ULT.



I'm not particularly good at macros, but I want to avoid creating lists of variables and then picking the ones I need out.


This isn't urgent, and I don't actually expect an answer, however I wouldn't regard my query as wrong or requiring a WARNING message.

[Patrick]

Hi John

Unfortunately in SQL you have to explicitly define the variables in the select clause if there are duplicate names.
Linus listed more or less the options of how to do this.

If a variable exists in more than one input table then it is taken from the first table in the join.

In the code you've posted the effect of duplicate vars is "only" a warning. It's more severe if it's a right join as common vars inclusive keys will be blank for non matching rows.

data table1;
do key = 1 to 3;
var1='a';
var2='b';
output;
end;
run;

data table2;
do key = 2 to 5;
var1='x';
var3='c';
output;
end;
run;

proc sql;
create table target as
select *
from
table2
right join
table1
on table1.key=table2.key
;
quit;

proc print data=target;
run;


HTH
Patrick Message was edited by: Patrick

[SASKiwi]

Here is another way of avoiding this warning:

proc sql;
create table a_comb (drop = same_col:) as
select *
from a1, a2 (rename = (msgid = same_col1))
where a1.msgid = a2.same_col1
;
quit;

If you rename all your repeated columns to same_col1, 2, 3 and so on, the DROP option will ensure they are not added to the new dataset. Its a bit tedious to set up but at least you can keep using a "SELECT *".

[polingjw · Accepted solution]

I don't think that it's documented, but you can use the nowarn option in proc sql to get rid of the warning message.

proc sql nowarn;
create table a_comb as
select *
from a1, a2
where a1.msgid = a2.msgid
;

Per @SASKiwi :

please note that the NOWARN option will block all warnings in that step, so if something unexpected happens you may end up blocking other warnings you may prefer to see. Also you are still left with the duplicate columns in the dataset. As long as you are OK with these limitations then no problem!

[JohnT]

Hi polingjw,

That's exactly what I was after !!!

Shame it's not documented.

I'm really curious as to how you found this.


Thanks very much for that !

[SASKiwi]

At the risk of being labelled picky, please note that the NOWARN option will block all warnings in that step, so if something unexpected happens you may end up blocking other warnings you may prefer to see. Also you are still left with the duplicate columns in the dataset. As long as you are OK with these limitations then no problem!