topic Re: Proc sql warning in SAS Procedures

Proc sql warning

stat_sas — Wed, 13 Mar 2013 20:50:01 GMT

Hi Experts,

Can anybody please help me to understand why I am getting warning message in the following proc sql?

Thanks in advance for your help.

Naeem

475 proc sql noprint;

476 create table all as

477 select a.*, s.* from

478 appended a, survey s

479 where a.id=s.id;

WARNING: Variable id already exists on file ALL

Re: Proc sql warning

Reeza — Wed, 13 Mar 2013 20:59:34 GMT

It's telling you that you have two variables that are the same between the two datasets. Considering you're joining on that field, you'd think it would be smart enough to know better.

It's preferable practice to specify the join type and use on rather than where, and also faster by about 5-10% in my tests. You'll still get the warning though, unless you explicitly list one set of variables.

proc sql noprint;

create table all as

select a.*, s.* from

appended a,

inner join survey s

on a.id=s.id;

Re: Proc sql warning

PGStats — Wed, 13 Mar 2013 21:05:59 GMT

When you say a.*, s.* , you want all the variables from appended and all the variables from survey. But the variable id exists in both tables, and it cannot exist twice with the same name in table all. If id is the only variable in both datasets, you can try this instead :

proc sql;

create table all as select * from appended natural join survey;

quit;

Re: Proc sql warning

Reeza — Wed, 13 Mar 2013 21:08:40 GMT

What happens in a natural join if you have more than 1 matching variable name?

Re: Proc sql warning

PGStats — Wed, 13 Mar 2013 21:13:10 GMT

The join is made on all matching (name, type) variables.

Re: Proc sql warning

stat_sas — Thu, 14 Mar 2013 00:27:42 GMT

Thanks PG!

It is working without any error message. As you suggested if there are more than one matching variables in two datasets then SAS will join 2 datasets based on all matching variables. Just for my understanding how natural join works for more than one matching variables?

Naeem

Re: Proc sql warning

PGStats — Thu, 14 Mar 2013 01:10:51 GMT

Say you have dataset T1 with variables A, B, C, X and dataset T2 with variables A, B, C, Y then the query

select * from T1 natural join T2;

is equivalent to

select T1.A, T1.B, T1,C, X, Y

from T1 inner join T2

on T1.A=T2.A and T1.B=T2.B and T1.C=T2.C;

Re: Proc sql warning

stat_sas — Thu, 14 Mar 2013 13:35:23 GMT

Thanks PG!

I got it. SAS is taking name and type of the variables to declare them matching. If matching variables contain duplicate or missing values how these will be handled by SAS.

Naeem

Re: Proc sql warning

PGStats — Thu, 14 Mar 2013 15:08:12 GMT

This is a vast subject. I recommend reading about all the available types of joins in SQL at :

SAS(R) 9.3 SQL Procedure User's Guide

and take some time to experiment on your own. The core concept (where you should start) is the cartesian product join.

Re: Proc sql warning

Reeza — Thu, 14 Mar 2013 15:13:14 GMT

This is also a great read.

A database professional’s best friend - The SAS Training Post

Re: Proc sql warning

stat_sas — Thu, 14 Mar 2013 16:30:15 GMT

Thanks all for the helpful suggestions on this topic.

Regards,

Naeem