topic Re: PROC SQL JOIN using an aliased colum in SAS Programming

PROC SQL JOIN using an aliased colum

anonymous_user — Mon, 19 Aug 2019 19:34:14 GMT

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;

Re: PROC SQL JOIN using an aliased colum

novinosrin — Tue, 20 Aug 2019 10:08:53 GMT

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;

Re: PROC SQL JOIN using an aliased colum

r_behata — Mon, 19 Aug 2019 20:13:19 GMT

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

Re: PROC SQL JOIN using an aliased colum

Reeza — Mon, 19 Aug 2019 21:12:20 GMT

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;

Re: PROC SQL JOIN using an aliased colum

anonymous_user — Mon, 19 Aug 2019 21:28:44 GMT

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

Re: PROC SQL JOIN using an aliased colum

anonymous_user — Mon, 19 Aug 2019 21:30:13 GMT

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.

Re: PROC SQL JOIN using an aliased colum

Reeza — Mon, 19 Aug 2019 21:36:51 GMT

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.

Re: PROC SQL JOIN using an aliased colum

novinosrin — Mon, 19 Aug 2019 23:47:21 GMT

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. 🙂

Re: PROC SQL JOIN using an aliased colum

Reeza — Tue, 20 Aug 2019 00:02:00 GMT

Thanks, Novinosrin, you are correct!

The calculation should be in the join condition as well.

Re: PROC SQL JOIN using an aliased colum

novinosrin — Tue, 20 Aug 2019 00:21:52 GMT

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;

Re: PROC SQL JOIN using an aliased colum

FreelanceReinh — Tue, 20 Aug 2019 07:12:16 GMT

@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) ...

Re: PROC SQL JOIN using an aliased colum

novinosrin — Tue, 20 Aug 2019 10:07:58 GMT

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