Proc Sql Errors are different based on the order of the tables that I am joining

[steffick]

Hi,

I'm having problems with this piece of code. I get different errors when I change the order of the tables I'm left joining.

proc sql;
CONNECT TO SQLSVR AS CDW(DATAsrc=<project name> &SQL_OPTIMAL.);

create table scr0 as select scrssn, 
	labchemresultnumericvalue as num, 
	labchemcompletedatetime as res, 
	upper(LabChemTestName) as test_name, 
	upper(c.topography) as topography, e.loinc, 35 as dsslarno
	
 connection to cdw

(select d.scrssn, a.patientsid, labchemresultnumericvalue
as num, labchemcompletedatetime as res, 
upper(LabChemTestName) as test_name, 
upper(c.topography) as topography, e.loinc from

src.chem_labchem as a

	left join CDWWork.dim.labchemtest as  b on  
		a.LabChemTestSID=b.labchemtestsid

	left join CDWWork.dim.Topography as c  on 
		a.TopographySID=c.TopographySID) 

	left join Src.CohortCrosswalk d on 
		a.patientsid=d.patientsid

	left join cdwwork.dim.loinc e on
		a.LOINCSID=e.LOINCSID;
quit;

When I have the tables in the order above (abcde) I get:

left join cdwwork.dim.loinc as e on

                                 22

                                 76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, ON.

I have other code that uses the same 3 part dataset name and it runs fine.

When I switch the datasets around to acbde or abdce:

left join CDWWork.dim.labchemtest as b on
                               _
                               22
                               76

When in aebdc order:

ERROR: Unresolved reference to table/correlation name c.
ERROR: Unresolved reference to table/correlation name e.
ERROR: The following columns were not found in the contributing tables: labchemcompletedatetime, labchemresultnumericvalue, LabChemTestName.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

[Tom]

Your tables names are not valid SAS dataset names.  If you want to refer to a dataset with a name instead of quoted physical path you can use with a one level name (for a WORK or perhaps a USER datasets) or a two level name.  You cannot use a three level name.

What datasets are you actually trying to query?

[SASKiwi]

You aren't closing your passthrough query correctly and repeating your SQL logic in SAS SQL outside of the passthrough section isn't a good idea. Try something like this:

proc sql;
CONNECT TO SQLSVR AS CDW(DATAsrc=<project name> &SQL_OPTIMAL.);

create table scr0 as 
select *
	
 from connection to cdw

(select d.scrssn, a.patientsid, labchemresultnumericvalue
as num, labchemcompletedatetime as res, 
upper(LabChemTestName) as test_name, 
upper(c.topography) as topography, e.loinc from

src.chem_labchem as a

	left join CDWWork.dim.labchemtest as  b on  
		a.LabChemTestSID=b.labchemtestsid

	left join CDWWork.dim.Topography as c  on 
		a.TopographySID=c.TopographySID) 

	left join Src.CohortCrosswalk d on 
		a.patientsid=d.patientsid

	left join cdwwork.dim.loinc e on
		a.LOINCSID=e.LOINCSID
);
quit;