ERROR: Function SUBSTR requires a character expression as argument 1. when linking by DOB

[DanielQuay]

Hello everyone,

So the problem I'm having is I get this error ERROR: Function SUBSTR requires a character expression as argument 1.

I'm trying to link two datasets based upon the Name and Date of Birth.

However, whenever I try to link based on the date of birth I get this ERROR.

Here is how I'm creating the DOB variable for HAVE1: dob_c = input(cat(de_bmo, de_bday, de_byr), mmddyy8.); 

The variable I am linking to is a numeric variable for date of birth in HAVE2.

proc sql;
create table WANT as
select a.*, cat(compress(medrecno),compress(uniqpati),dis_date) as unique_id_ev, b.yod_cert
       from HAVE2 as a

       left join HAVE1 as b on compress(a.patiname) = cat(compress(b.de_lname),',',compress(b.de_fname))
       and a.bth_date = substr(b.dob_c,1,2)||substr(b.dob_c,4,2)||substr(b.dob_c,7,4)
       where b.de_fname is not null and cat(compress(medrecno),compress(uniqpati),dis_date) not in (select unique_id_ev from out_2017)
      and dispos ne '20' and input(a.dis_date,mmddyy8.) < b.deathdate;
quit;

I'm really not good with PROC SQL coding so any help you can give me here would be appreciated.

[Kurt_Bremser]

Since you create dob_c with a date informat, it is a SAS date variable (numeric, counts days from 1960-01-01). If bth_date is also a SAS date value, you can compare them directly. If it is not a SAS date value but represents a date, then convert it first.

[PaigeMiller]

This is not an SQL problem. This is a SUBSTR problem. You need to have a character expression as argument 1. You get this error message because in one (or more) of your calls to SUBSTR, you have a NUMERIC variable.

Also, you will find your coding is much easier if you don't try to pull apart dates via constructs such as 

substr(b.dob_c,1,2)||substr(b.dob_c,4,2)||substr(b.dob_c,7,4)

Treat dates as numeric dates, and all of these problems go away.

[Reeza]

Use the MDY() function to create your date instead of the INPUT().
Did you double check the results of CAT()? It often introduces spaces so usually you should use CATT() instead.

[DanielQuay]

Thanks for the pointer everyone.  I don't have it kicked entirely yet but these have been really good insights.  
I also learned that the codebook I was using mislabeled the HAVE2 data as numeric, it's character that's an insight I could've used before posting.

[ballardw]

---

@DanielQuay wrote:

Thanks for the pointer everyone.  I don't have it kicked entirely yet but these have been really good insights.  
I also learned that the codebook I was using mislabeled the HAVE2 data as numeric, it's character that's an insight I could've used before posting.

---

You may want to consider running Proc Contents on your Have2 data set and compare everything to your code book. One error such as this for a common variable concept like date of birth makes me cringe.

And dates should be numeric but as SAS date values with an appropriate date format assigned, not character.

Character dates seldom sort properly and basically never graph properly plus use for things like computing age at a given date or intervals between values is obnoxious at best and generally means creating a date value as an intermediate step.