Help using Base SAS procedures

Proc sql warning

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,204
Accepted Solution

Proc sql warning

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


Accepted Solutions
Solution
‎03-13-2013 05:05 PM
Respected Advisor
Posts: 4,644

Re: Proc sql warning

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;

PG

PG

View solution in original post


All Replies
Super User
Posts: 17,801

Re: Proc sql warning

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;

Solution
‎03-13-2013 05:05 PM
Respected Advisor
Posts: 4,644

Re: Proc sql warning

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;

PG

PG
Super User
Posts: 17,801

Re: Proc sql warning

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

Respected Advisor
Posts: 4,644

Re: Proc sql warning

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

PG
Trusted Advisor
Posts: 1,204

Re: Proc sql warning

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

Respected Advisor
Posts: 4,644

Re: Proc sql warning

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;

PG

PG
Trusted Advisor
Posts: 1,204

Re: Proc sql warning

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

Respected Advisor
Posts: 4,644

Re: Proc sql warning

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.

PG

PG
Super User
Posts: 17,801

Re: Proc sql warning

Trusted Advisor
Posts: 1,204

Re: Proc sql warning

Thanks all for the helpful suggestions on this topic.

Regards,

Naeem

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 419 views
  • 2 likes
  • 3 in conversation