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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: