Trying to convert a char into numeric

[wheddingsjr]

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? 

[jimbarbour · Accepted solution]

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

[wheddingsjr]

Thanks Jim

That worked perfectly!!!

[ballardw]

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.

[wheddingsjr]

Thanks Ballard. That did the trick.

[Kurt_Bremser]

The table alias must immediately preceed the variable name:

input(I.PatientIdentityID, best.)

[wheddingsjr]

Thanks Kurt. That worked.