Help using Base SAS procedures

SQL language

Reply
Occasional Contributor
Posts: 12

SQL language


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

Valued Guide
Posts: 858

Re: SQL language

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.

Super User
Posts: 9,681

Re: SQL language

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

Super User
Super User
Posts: 7,401

Re: SQL language

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.

Ask a Question
Discussion stats
  • 3 replies
  • 262 views
  • 0 likes
  • 4 in conversation