Need to filter a condition in Proc sql

[ambadi007]

Hi ,

I need to put  a condition in my sql code for not fetching the vlaue 'P' for a column clm_ind, tried<> and also not in in the where conditions but its not working. The column in charector with a length of 1. Tried with strip(clm_ind) not in 'P' but not working . Please advice me what would be the problem

Thanks.

[Reeza]

Please show your code and ideally data that replicates the issue.

[ambadi007]

WHERE
	CL.CL_CLAIM_STATUS_CD IN ('O', 'R') 
	AND (CL.CL_CONV_CLAIM_IND) <>'P'

The above (claim_status) condition is working but the claim conv not working

The conv column has values like P,Blank and N

[Reeza]

I would try the SAS 'not equals' operator, although I doubt that's the issue. 

Check the data type/format. Is there a format applied, if so, you need the underlying value. Or check if there's trailing spaces with TRIM()

where trim(x) ne 'P' 

[ambadi007]

Hi used the code but stil no luck. The data format is charector ($1.)

[LinusH]

By the loonof the eye this aboukd work. The p isn't in lowcase...?

[ambadi007]

Hi Team , the below code I applied and it got worked , Could somebody explains me how it worked

COALESCE(CL_CONV_CLAIM_IND,'') <> 'P'

[AlanC · Accepted solution]

Your working code is a random win, it is not the solution or the issue.

Try this (pseudocode since I don't have SAS currently):

WHERE UPCASE(SUBSTR(STRIP(CL_CONV_CLAIM_IND),1,1) ) <> 'P'

That will tell us what is happening. My guess is that the claim code field is not a length of 1 but a format of $1 so it looks like 1 when it is not.