topic Re: Trying to convert a char into numeric in SAS Programming

Trying to convert a char into numeric

wheddingsjr — Fri, 11 Jun 2021 16:22:54 GMT

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?

Re: Trying to convert a char into numeric

jimbarbour — Fri, 11 Jun 2021 16:28:18 GMT

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

Re: Trying to convert a char into numeric

ballardw — Fri, 11 Jun 2021 17:13:45 GMT

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.

Re: Trying to convert a char into numeric

Kurt_Bremser — Fri, 11 Jun 2021 16:30:55 GMT

The table alias must immediately preceed the variable name:

input(I.PatientIdentityID, best.)

Re: Trying to convert a char into numeric

wheddingsjr — Mon, 14 Jun 2021 11:08:41 GMT

Thanks Jim

That worked perfectly!!!

Re: Trying to convert a char into numeric

wheddingsjr — Mon, 14 Jun 2021 11:09:57 GMT

Thanks Ballard. That did the trick.

Re: Trying to convert a char into numeric

wheddingsjr — Mon, 14 Jun 2021 11:11:57 GMT

Thanks Kurt. That worked.