02-14-2017 04:53 PM
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?
02-14-2017 05:02 PM
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.
02-14-2017 06:04 PM
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.
02-14-2017 06:29 PM
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;
02-15-2017 05:40 AM
02-15-2017 05:58 AM
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.
02-15-2017 05:59 AM - edited 02-15-2017 06:05 AM
What do you mean by
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
02-15-2017 08:10 AM
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.
02-14-2017 06:04 PM