SQL language

[boban]

Hey!

I typed the program bellow and got the following warning message:

WARNING: Variable AdNo already exists on file WORK.SQL_Female

proc sql;

create table sql female as

select L.*, R.*

from reduced reduced_FinalParentage20022006 as L

Left join HeterozygAgilisrhhadults as R

on L.adno=R.adno;

quit;

Basically, it is a left join with adno being the only  common variable (otherwise, the 2 data sets have different variables).

Does anyone know why this may happen and if it has any importance (the sql table was created successfully after all).

Many thanks,

Boban

[Steelers_In_DC]

You are selecting the same variable from both datasets, you only need one.  If you want to eliminate this warning you should list all of the variables that you want from each dataset.  If you leave it like this you will pull that variable form the L. dataset.  If these are very 'wide' datasets and you don't want to list all of the variables that you need then you'll have to get used to the warning.

[Ksharp]

As log said ADNO exists in two table , You should drop one of them .

proc sql;

create table sql female as

select L.*, R.v1,R.v2,...........

from reduced reduced_FinalParentage20022006 as L

Left join HeterozygAgilisrhhadults as R

on L.adno=R.adno;

quit;

OR rename it as a unique name :

proc sql;

create table sql female as

select *

from reduced reduced_FinalParentage20022006 as L

Left join HeterozygAgilisrhhadults(rename=(adno=_adno)) as R

on L.adno=R._adno;

quit;

Xia Keshan

[RW9]

This is a good example of why not to use the * notation.  You should know what variables you want to select from each table.

xia keshan, whilst its possible to add in SAS specific syntax to SQL statements, it may not be the best idea.  It could be that this SQL gets moved to a database or other processing, and could create confusion as to why it does not work.