Desktop productivity for business analysts and programmers

requires a character expression as argument 1

Reply
Occasional Contributor
Posts: 19

requires a character expression as argument 1

Hi I'm not sure what is causing my program to recieve this error, is it because of the data sets contains numeric variables?

 

 

format $15. ,b.indv_id ,b.MP_MANAGEDPERSONID ,b.MP_BIRTHDATE as _DOB ,b.MP_zip as _zip from outlib.MCP_QUAL as a left join
refdata.xwalk_careone_crm as b on UPCASE(COMPRESS(strip(a.PtFirst))) = UPCASE(COMPRESS(strip(b.MP_FIRSTNAME))) and
UPCASE(COMPRESS(strip(a.PtLast))) = UPCASE(COMPRESS(strip(b.MP_LASTNAME))) ;
ERROR: Function STRIP requires a character expression as argument 1.
ERROR: Function STRIP requires a character expression as argument 1.

 

Original code

 

 create table ALL_NPART as
          select distinct
               a.*
               ,b.MP_FIRSTNAME as _Fname format $15.
               ,b.MP_LASTNAME as _Lname  format $15.
               ,b.indv_id
               ,b.MP_MANAGEDPERSONID
               ,b.MP_BIRTHDATE as _DOB
               ,b.MP_zip as _zip
          from outlib.MCP_QUAL as a
          left join refdata.xwalk_careone_crm as b
           on UPCASE(COMPRESS(strip(a.PtFirst))) = UPCASE(COMPRESS(strip(b.MP_FIRSTNAME)))
               and UPCASE(COMPRESS(strip(a.PtLast))) = UPCASE(COMPRESS(strip(b.MP_LASTNAME)))
     ;quit;

PROC Star
Posts: 1,167

Re: requires a character expression as argument 1

Yup. You can use put(a.PtFirst, best15.) etc. to convert your numeric variables to character.

 

Tom

PROC Star
Posts: 1,167

Re: requires a character expression as argument 1

That is, unless any of the numbers have fractions. If they do, you're in trouble. Matching fractional numbers in SQL is very problematic.

 

Tom

Super User
Super User
Posts: 7,074

Re: requires a character expression as argument 1

You keep asking the same question. The answer is no different today than it was yesterday. 

From the little bit of code you posted the most obvious thing is that your variables are NOT character string.

Run proc contents on your input datasets and see which of the four variables that you are passing to the STRIP() function are numeric.

 

Usually this happens when you use PROC IMPORT to guess at how your dataset should be created. If you have character variable in a CSV file and all of the values are missing then PROC IMPORT will assume that the variable is numeric, since there is nothing that tells it otherwise.  The solution is to NOT use PROC IMPORT to guess at what is in your CSV file. Instead write a data step to read the CSV file so that you have full control over how the variables as defined.

Super User
Posts: 7,832

Re: requires a character expression as argument 1

KNOW.YOUR.DATA.

 

Inspect the type and contents of your variables to see if they match your expectations.

As the ERROR message very clearly states, some of your variables are of type numeric, and can't be used in a function that is for character values only.

So look which column you used in the join condition has the wrong type.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 35

Re: requires a character expression as argument 1

Posted in reply to KurtBremser

I did look at all 4 variables and my data all is character variables, ptfirst and ptlast and as well as the other first and last name variables. I did a proc contents and that was what is shown

Super User
Super User
Posts: 7,074

Re: requires a character expression as argument 1

Show us how you checked the variable's type.  Most likely you are either looking at the wrong variables or perhaps even the wrong datasets.  

 

Another really easy way to get a numeric variable instead of character is if you accidently defined it that way. Perhaps by mistyping the variable name.  Say your input dataset had a variable named FIRST_NAME.  Then you wrote a little datastep say to subset the data or the variables.

 

data mydata;
  set mydata;
 keep id firstname last_name ;
run;

You would get a note in the log that the new variable FIRSTNAME (without the _ in the middle) is uninitialized and SAS will make it a numeric variable. So now if later on you try to apply STRIP(FIRSTNAME) you will either get an error (proc sql) or a note that numbers have been converted to characters (data step).

Super User
Super User
Posts: 7,977

Re: requires a character expression as argument 1

You have several threads which are all pretty much the same question open.  Plenty of advice in those, so read through them thoroughly, and select a response and mark it as correct.  

 

For future posts follow the guidance you will see below to the Post button when creating a new post:

- Show test data in the form of a datastep - this is a key piece of information as we cannot see what you are working on.  Follow this post if you need help to do so:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

- Show what the expected outcome should be

- Show full code associated with the transformation, this could be one or multiple steps

- Show the log (all relevant parts) if there are notes/warnings/errors.

Ask a Question
Discussion stats
  • 7 replies
  • 604 views
  • 0 likes
  • 6 in conversation