Converting char into numeric

[wheddingsjr]

Hello all

I received this error when trying to run this code:

35         PROC SQL;
36         CREATE TABLE work.chemo AS
37         SELECT
38         	CASE WHEN Month(input(DischargeDTS,yymmdd10.)) > 9
39         		THEN YEAR(input(DischargeDTS,yymmdd10.)) + 1
40         		ELSE YEAR(input(DischargeDTS,yymmdd10.))
41         	END AS fiscal_year
42         , I.PatientIdentityID as MRN
43         , DischargeDTS AS date
44         , HospitalAccountID
45         FROM epicfin.HospitalAccount a
46         LEFT JOIN EPICPAT.Identity I on a.PatientID = I.PatientID
47         WHERE PatientIdentityID IN (SELECT mrn FROM ed.all_mrn)
48         ;
ERROR: Expression using IN has components that are of different data types.

I checked the tables and see that PatientIdentityID is char but ed.all_mrn is numeric. How do I convert the PatientIdentityID into numeric so that this code runs? Any help wold be appreciated.

Thanks

[PaigeMiller]

WHERE input(PatientIdentityID,16.) IN (SELECT mrn FROM ed.all_mrn)

You may or may not need to use 16 digits for PatientIdentityID, but if there are fewer digits, feel free to modify the code above.

[wheddingsjr]

Thanks PaigeMiller. That worked perfectly.

[Reeza]

WHERE input(PatientIdentityID, best.) IN (SELECT mrn FROM ed.all_mrn)

[wheddingsjr]

Thanks Reeza...that worked perfect. Rather than use a particular length I used best and it was perfect.

[Tom · Accepted solution]

---

@wheddingsjr wrote:
Thanks Reeza...that worked perfect. Rather than use a particular length I used best and it was perfect.

---

Note that BEST is the name of a FORMAT.  If you use it as in INFORMAT you will just get the normal w.d informat instead of any "best" informat.   Just use 32. as the informat.  32 is the maximum number of bytes that informat supports and the INPUT() function does not care if the width used on the informat is more bytes than there are in the string being read.  So unless there are trailing characters in the string you do not want to read there is no reason to use something shorter.  Do NOT add a decimal place value to the informat unless you know that the decimal was purposely excluded from the string and you want to automatically divide any values without an explicit decimal point by the corresponding power of ten in order to place the missing decimal point in the right place.

[Tom]

Matching numbers is easier since character strings might have different numbers of leading zeros or leading spaces.

WHERE input(PatientIdentityID,32.) IN (SELECT mrn FROM ed.all_mrn)

Hopefully you do not have ids with significant leading zeros, or more than 15 digits.  If so the values in the mrn variable in the ed.all_mrn dataset are probably already wrong.

[wheddingsjr]

Thanks Tom, as I told the others this worked perfectly. Is there a way to accept everyone's response as a solution. I want to give credit to you all.