Hello all
A few weeks ago I got some assistance in converting char into numeric and it worked. I am trying to use that same advice on another project but its not working. I am obviously not applying it correctly and not sure how or why. This is the code.
PROC SQL;
CREATE TABLE all_mrn AS
SELECT *
FROM epicfin.HospitalAccount a
LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
RIGHT JOIN ed.all_mrn m on I.PatientIdentityID = m.mrn
;QUIT;
With this code I get this error:
26 PROC SQL;
27 CREATE TABLE all_mrn AS
28 SELECT *
29 FROM epicfin.HospitalAccount a
30 LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
31 RIGHT JOIN ed.all_mrn m on I.PatientIdentityID = m.mrn
32 ;
ERROR: Expression using equals (=) has components that are of different data types.
I checked and found out that I.PatientIdentityID is char while m.mrn is numeric so I changed the code to:
PROC SQL;
CREATE TABLE all_mrn AS
SELECT *
FROM epicfin.HospitalAccount a
LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
RIGHT JOIN ed.all_mrn m on I.input(PatientIdentityID, best.) = m.mrn
;QUIT;
These are the error messages I received:
34 PROC SQL;
35 CREATE TABLE all_mrn AS
36 SELECT *
37 FROM epicfin.HospitalAccount a
38 LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
39 RIGHT JOIN ed.all_mrn m on I.input(PatientIdentityID, best.) = m.mrn
_ _
22 76
ERROR 22-322: Syntax error, expecting one of the following: ',', JOIN.
ERROR 76-322: Syntax error, statement will be ignored.
2 The SAS System 11:46 Friday, June 11, 2021
39 ! RIGHT JOIN ed.all_mrn m on I.input(PatientIdentityID, best.) = m.mrn
_
22
ERROR 22-322: Expecting a name.
What am I doing wrong?
Try putting the "I" inside the parenthesis.
Like this:
PROC SQL;
CREATE TABLE all_mrn AS
SELECT *
FROM epicfin.HospitalAccount a
LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
RIGHT JOIN ed.all_mrn m on input(I.PatientIdentityID, best.) = m.mrn
;QUIT;
Jim
Try putting the "I" inside the parenthesis.
Like this:
PROC SQL;
CREATE TABLE all_mrn AS
SELECT *
FROM epicfin.HospitalAccount a
LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
RIGHT JOIN ed.all_mrn m on input(I.PatientIdentityID, best.) = m.mrn
;QUIT;
Jim
Thanks Jim
That worked perfectly!!!
Instead of
39 RIGHT JOIN ed.all_mrn m on I.input(PatientIdentityID, best.) = m.mrn
try
RIGHT JOIN ed.all_mrn m on input(I.PatientIdentityID, best.) = m.mrn
What happens when you use I.input is that you are telling SAS that the alias data set I has a variable named input and the "( "is in the "wrong place" following a variable.
Thanks Ballard. That did the trick.
The table alias must immediately preceed the variable name:
input(I.PatientIdentityID, best.)
Thanks Kurt. That worked.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.