BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8

Hi when i use join for two table i know i van use select a. b. To specify where to get the table. For example

Select a.apple a.lemon b.size b.colour
weight origin
from fruit a left join info b on a.cost=b.cost

But what if I have some variable where no a. Or b like weight and origin in the above Next to it, how do i know where these variAble is from?

6 REPLIES 6
HeatherNewton
Quartz | Level 8
I know it is from a, but what i dont underratand is why for some u put a. and for some u dont have to.. and it knows where to get it..
fja
Lapis Lazuli | Level 10 fja
Lapis Lazuli | Level 10

As a rule of thumb if the column is unambigous you do not have to qualify the table using a qualified name. If there is some ambiguity (eg. both tables contain a column called bla) you have to use the a.bla ... to specify the column to select.

 

Maybe you have a little play in SAS and try it yourself: 


data work.TestData1;
	input ID A$ B$ C$ bla$17.;
	datalines;
	1 a1 b1 c1 bla1_fromTableOne
	2 a2 b2 c2 bla2_fromTableOne
	;
run;
data work.TestData2;
	input ID D$ E$ F$ bla$17.;
	datalines;
	1 d1 e1 f1 bla1_fromTableTwo
	2 d2 e2 f2 bla2_fromTableTwo
	;
run;

proc sql;
	select A, B, C, D, Two.bla from TestData1 as One, TestData2 as Two where One.ID = Two.ID;
	quit;
run;

--fja

 

fja
Lapis Lazuli | Level 10 fja
Lapis Lazuli | Level 10

Hello!

 

Though I completely agree with Kurt's answer above, I find this topic intresting enough to add some thoughts.

 

This is tricky! What you do by the join is that you assemble a new table ... the naming of its columns are derived from the qualified and unqualified names ... or AS expressions if given. 

From that, any unqualified name in the select part could origine from any source table and is not determined by the select statement - if given in the form quoted.

For short: As @Kurt_Bremser has aready put it, you need to have a look at your data. Now it is that tables happen to change and columns get added form time to time. For that reason my suggestion would be to use qualified names whenever possible. ... but this is of course beyond the question posed.

 

--fja

 

s_lassen
Meteorite | Level 14

how do i know where these variAble is from?

You don't. Not unless you look at the input data. Your example code shows a lot about how NOT to do things in SQL:

  • Table aliases are just "a" and "b", not very telling. It gets really horrible when you have large joins. I once had to debug a join where the aliases went all the way from "a" to "k", and when some of the tables entered the joins more than once, the first instance was called "i" and the second "ii". Not very readable.
  • It is not specified where all columns come from
  • program is badly formatted

So I would probably rewrite it as something like this (just guessing as to where the unspecified columns come from):

Select 
  fruit.apple,
  fruit.lemon
  info.size,
  info.colour,
  info.weight,
  info.origin
from fruit left join info 
  on fruit.cost=info.cost
PGStats
Opal | Level 21

You can use the FEEDBACK option:

 

proc SQL feedback;

Select a.apple, a.lemon, b.size, b.colour,
weight, origin
from fruit a left join info b on a.cost=b.cost;

quit;

 

it will expand the query in full details.

PG

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1649 views
  • 3 likes
  • 5 in conversation