topic Re: Need to filter a condition in Proc sql in SAS Programming

Need to filter a condition in Proc sql

ambadi007 — Thu, 20 Jul 2017 03:33:30 GMT

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.

Re: Need to filter a condition in Proc sql

Reeza — Thu, 20 Jul 2017 03:36:54 GMT

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

Re: Need to filter a condition in Proc sql

ambadi007 — Thu, 20 Jul 2017 04:06:24 GMT

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

Re: Need to filter a condition in Proc sql

Reeza — Thu, 20 Jul 2017 04:23:23 GMT

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'

Re: Need to filter a condition in Proc sql

ambadi007 — Thu, 20 Jul 2017 05:07:39 GMT

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

Re: Need to filter a condition in Proc sql

LinusH — Thu, 20 Jul 2017 06:33:50 GMT

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

Re: Need to filter a condition in Proc sql

ambadi007 — Thu, 20 Jul 2017 08:18:00 GMT

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

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

Re: Need to filter a condition in Proc sql

AlanC — Thu, 20 Jul 2017 14:04:33 GMT

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.