topic Re: SQL language in SAS Procedures

SQL language

boban — Tue, 19 May 2015 08:05:44 GMT

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

Re: SQL language

Steelers_In_DC — Tue, 19 May 2015 12:13:20 GMT

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.

Re: SQL language

Ksharp — Tue, 19 May 2015 12:20:04 GMT

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

Re: SQL language

RW9 — Tue, 19 May 2015 12:25:15 GMT

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.