PROC SQL ON statement is rejecting a full dataset name period column name but accepts a single qual

[lchristensen]

I am taking an Udemy advanced SAS course. An exercise for the PROC SQL has me doing two PROC IMPORTS and then a PROC SQL using the ON clause.

In the first part of the attached code I have the process working. The import and SQL are only using partial dataset names (that is: phd0 or pag0) where I like to use full names even for work datasets so I have 'Work.Patient_HD' and 'Work.Patient_Age.' In the ON statement when I have the dataset names of 'Work.Patient_HD.Patient_id' the proc fails with:

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND,

ANSIMISS, BETWEEN, CONTAINS, CROSS, EQ, EQT, EXCEPT, FULL, GE, GET, GROUP, GT, GTT, HAVING, IN, INNER, INTERSECT, IS,

JOIN, LE, LEFT, LET, LIKE, LT, LTT, NATURAL, NE, NET, NOMISS, NOT, NOTIN, OR, ORDER, OUTER, RIGHT, UNION, WHERE, ^,

^=, |, ||, ~, ~=.

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

The PROC IMPORTs are fine.

In the PROC SQL the same code where I use only the partial names ( phd0.Patient_id   and pag0.Patient_id) works fine. That is the class solution provided.

I'm trying to understand why putting in the full  name of   'Work.Patient_ID.Patient_id' fails where the 'phd0.Patient_ID' works.

I'm having some trouble with getting stuff from SAS On Demand for Academics downloaded to someplace that I can the attach the code to this site.

The code (working and not) is:

/* Coding exercise 27 for SQL */
/* The below works */
proc import datafile = '/home/lcwrite/data/Patient_HD.xlsx'
dbms=xlsx
out = phd0;
run;
proc import datafile = '/home/lcwrite/data/Patient_age_gender.xlsx'
dbms=xlsx
out = pag0;
run;
proc sql feedback;
select *
from phd0, pag0
where phd0.Patient_ID = pag0.Patient_ID
;
quit;
proc sql feedback;
select *
from phd0 INNER JOIN
pag0
on phd0.Patient_id = pag0.Patient_id
order by Patient_ID;
quit;

/* ------------------------------------------------------------------------------------------------------------------- */
/* The below does not work */
proc import datafile = '/home/lcwrite/data/Patient_HD.xlsx'
dbms=xlsx
out = Work.Patient_HD;
run;
proc import datafile = '/home/lcwrite/data/Patient_age_gender.xlsx'
dbms=xlsx
out = Work.Patient_AGE;
run;
proc sql feedback;
select *
from Work.Patient_HD, Work.Patient_AGE
where Work.Patient_HD.Patient_ID = Work.Patient_AGE.Patient_ID
;
quit;

/* The error 22 points to the decimal in 'Work.Patient_HD.Patient_id' */

[Tom · Accepted solution]

An SQL table reference alias cannot contain a period.  If you use a two level dataset name

from libref.memname

then only the member part is used to set the default alias.

Either use the alias that SAS will understand,

from libref.mem1
where mem1.var1 is not null

or assign your own.

from libref.mem1 alias
where alias.var1 is not null

[PaigeMiller]

From now on, when you have an error in a PROC, show us the log for that PROC. We need to see the code as it appears in the log, and any messages such as errors, warnins and noes. DO NOT show us just the error messages in the log, detached from the code in the log.

where Work.Patient_HD.Patient_ID = Work.Patient_AGE.Patient_ID
;

Try 

where patient_hd.patient_id =patient_age.patient_id;