PROC SQL JOIN using an aliased colum

[#1]

I have the following query where I try to do a LEFT JOIN on an aliased column.

I get the following errors

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

How can I join using an aliased column name?

PROC SQL;
SELECT a.user_id, substr(a.item_id, 1, 3) as a.department, a.item_id, b.dept_name
FROM sas.db a
LEFT JOIN sas.db_2 b 
ON b.department_id=a.department
WHERE a.invoice_date>='18AUG2019'd;
QUIT;

[novinosrin · Accepted solution]

Try

PROC SQL;
SELECT a.user_id, substr(a.item_id, 1, 3) as department, a.item_id, b.dept_name
FROM sas.db a
LEFT JOIN sas.db_2 b 
ON b.department_id=substr(a.item_id, 1, 3)
WHERE a.invoice_date>='18AUG2019'd;
QUIT;

or even better to have an inline-view

PROC SQL;
SELECT a.*, b.dept_name
FROM (select user_id, substr(item_id, 1, 3) as department, item_id,invoice_date from sas.db) a
LEFT JOIN sas.db_2 b 
ON b.department_id=a.department
WHERE a.invoice_date>='18AUG2019'd;
QUIT;

[FreelanceReinh]

---

@novinosrin wrote:

(...)

or even better to have an inline-view

PROC SQL;
SELECT a.*, b.dept_name
FROM (select user_id, substr(a.item_id, 1, 3) as department, item_id,invoice_date from sas.db) a
...

---

Hi @novinosrin,

Now that you've pulled my attention to this thread, I would like to point out a minor correction to the accepted solution: Either the inline-view must define the alias a or variable item_id from dataset sas.db needs to be referenced without an alias:

... substr(item_id, 1, 3) ...

[novinosrin]

Yes Sir, So true. I can't believe I overlooked that 🙂

[r_behata]

PROC SQL;
SELECT a.user_id, substr(a.item_id, 1, 3) as department, a.item_id, b.dept_name
FROM sas.db a
LEFT JOIN sas.db_2 b 
ON b.department_id=a.department
WHERE a.invoice_date>='18AUG2019'd;
QUIT;

Change : substr(a.item_id, 1, 3) as department

[#6]

ERROR: Column department could not be found in the table/view identified with the correlation name A.

[Reeza]

The issue isn't the JOIN, the issue is that you're trying to name a newly created column with an alias which doesn't make sense.

substr(a.item_id, 1, 3) as a.department

Remove a. from the department and it will create the new variable. 

---

@anonymous_user wrote:

I have the following query where I try to do a LEFT JOIN on an aliased column.

I get the following errors

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

How can I join using an aliased column name?

PROC SQL;
SELECT a.user_id, substr(a.item_id, 1, 3) as a.department, a.item_id, b.dept_name
FROM sas.db a
LEFT JOIN sas.db_2 b 
ON b.department_id=a.department
WHERE a.invoice_date>='18AUG2019'd;
QUIT;

---

[#8]

ERROR: Column department could not be found in the table/view identified with the correlation name A.

When I try your solution, this is the error I get. 

[Reeza]

Did you adjust the JOIN condition? 

You either need to put the formula there as well, or you need to add the key word CALCULATED in front of it. 

Post the code you're using if you're having issues, with the log preferably. If we can't see what you've ran, we're really just guessing at causes.

---

@anonymous_user wrote:

ERROR: Column department could not be found in the table/view identified with the correlation name A.

When I try your solution, this is the error I get. 

---

[novinosrin]

Hi @Reeza   

The thumb rule is ON condition associates with FROM . In other words

When we say Hey Proc sql, Please select the columns from the tables(this means sql processes the FROM and ON , keeps the copy(resulting FROM-ON execution)  like that of a PDV ) from which SELECT chooses the columns to print or write to the OUTPUT table. In many cases offering advantages to do column wise/row wise operations, of course the disadvantage being it is highly I/O dependent/intensive. 

Therefore to conclude, the computed column in SELECT clause cannot be used ON join condition whether or not with a CALCULATED Keyword. Of course you can circumvent with an  in line view , albeit that's another pre-pass. The computed aka CALCULATED column can be used in GROUP BY clause but that's so obvious because it anyways makes it self explanatory that it groups the FROM or FROM-ON result. In essence FROM tables/FROM-ON is a unique independent block so to speak, whose result forms the base for other clauses to act upon.

.

HTH

PS Forgive me if my writing isn't great. I wish I had the lexicon of @hashman  / @FreelanceReinh  to compile sentences so eloquently and  yet quoting references. Well, hopefully in time. I am so jealous. 🙂

[Reeza]

Thanks, Novinosrin, you are correct!

The calculation should be in the join condition as well.

[novinosrin]

Finally this illustration is for my mom(for teaching me SQL) and my recently departed father's soul. To whom I owe everything

data one;
input var $20.;
cards;
hope floats
hope sinks
hope
;


data two;
input var1 $ var2 & $20.;
cards;
hope  sad and happy
;

/*Expression=variable success*/
proc sql;
create table want as
select a.var,scan(a.var,1) as newvar,var2
from one a left join two b
on scan(a.var,1)=var1;
quit;

/*Calculated newvar-error*/
proc sql;
create table want as
select a.var,scan(a.var,1) as newvar,var2
from one a left join two b
on calculated newvar=var1;
quit;

/*Without Calculated newvar-error*/
proc sql;
create table want as
select a.var,scan(a.var,1) as newvar,var2
from one a left join two b
on  newvar=var1;
quit;

/*In line View success*/
proc sql;
create table want as
select a.*,var2
from (select var,scan(var,1) as newvar from one) a left join two b
on  newvar=var1;
quit;