Help using Base SAS procedures

proc sql left join error - column could not be found

Reply
Frequent Contributor
Posts: 110

proc sql left join error - column could not be found

I am getting this error:

 

Column SomeColumn could not be found in the table/view identified with the correlation name TRANS.

 

Using this code

 

proc sql;
CREATE TABLE WORK.Test AS
select
Trans.SomeColumn 
from WORK.Joiner AS J
left outer join work.parameterestimatestrans AS Trans
ON J._NAME_ = Trans._NAME_;
run;

I know that SomeColumn is not in the table aliased Trans. However, a left out join should simply return a null (i.e. . in SAS). So I am a bit confused about the non sql sas behaviour. Any ideas?

Super User
Posts: 7,758

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn

The error is exactly what should happen. You explicitly named a column in the select that does not exist, so your select statement is useless.

SQL does not automatically create variables with missing values, as the data step does.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn
Would you mind creating the datastep equivalent please?
Super User
Posts: 11,343

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn

csetzkorn wrote:
Would you mind creating the datastep equivalent please?

It might help if you describe what the output should be. From the code posted it only appears that you want a data set with single variable and all values missing.

 

Super User
Posts: 19,768

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn

Here, I fixed it for you, with a numeric and character missing. 

 

proc sql;
CREATE TABLE WORK.Test AS
select
" " as SomeColumn,
. as NumSomeColumn
from WORK.Joiner AS J
left outer join work.parameterestimatestrans AS Trans
ON J._NAME_ = Trans._NAME_;
run;
Frequent Contributor
Posts: 110

Re: proc sql left join error - column could not be found

Thanks getting closer. The challenge I have that work.parameterestimatestrans may contain NumSomeColumn. I only want to set NumSomeColumn to 'null'/. if work.parameterestimatestrans does not exist.
Frequent Contributor
Posts: 110

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn

I found a solution to my problem. Using:

 

data work.parameterestimatestrans;
    set work.parameterestimatestrans;
	NumSomeColumn = coalesce(NumSomeColumn,.);
run;

Will impute the variable should it be missing and the left join picks this up.

Super User
Posts: 7,758

Re: proc sql left join error - column could not be found

[ Edited ]
Posted in reply to csetzkorn

What do you mean by


csetzkorn wrote:
 if work.parameterestimatestrans does not exist.

- the dataset does not exist

- or the dataset exists, but there is no matching record in it

- or the column does not exist in the dataset

?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: proc sql left join error - column could not be found

Posted in reply to KurtBremser
I meant if NumSomeColumn does not exist in work.parameterestimatestrans
Super User
Posts: 19,768

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn

If it doesn't exist the COALESCE doesn't do anything. 

It assigns a missing value. 

 

Super User
Posts: 7,758

Re: proc sql left join error - column could not be found

Since the OP has finally stated his/her true intentions, I can say that the data step with coalesce() is the correct solution.

The OP wants to reach a state where the variable will be present, no matter what is in the original dataset.

If the variable is not there, it will be created as missing, if it is there, the values will be preserved.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,424

Re: proc sql left join error - column could not be found

Posted in reply to csetzkorn
There is no exact equivalent between SQL and the data step. Isn't it easier to fix the existing query?
If you want to create a column with missing values specify it as a constant in the select clause.
Data never sleeps
Ask a Question
Discussion stats
  • 11 replies
  • 418 views
  • 3 likes
  • 5 in conversation