DATA Step, Macro, Functions and more

Need to filter a condition in Proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Need to filter a condition in Proc sql

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.


Accepted Solutions
Solution
a month ago
Contributor
Posts: 62

Re: Need to filter a condition in Proc sql

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. 

View solution in original post


All Replies
Super User
Posts: 17,963

Re: Need to filter a condition in Proc sql

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

Contributor
Posts: 60

Re: Need to filter a condition in Proc sql

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

Super User
Posts: 17,963

Re: Need to filter a condition in Proc sql

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' 
Contributor
Posts: 60

Re: Need to filter a condition in Proc sql

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

Super User
Posts: 5,260

Re: Need to filter a condition in Proc sql

By the loonof the eye this aboukd work. The p isn't in lowcase...?
Data never sleeps
Contributor
Posts: 60

Re: Need to filter a condition in Proc sql

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

COALESCE(CL_CONV_CLAIM_IND,'') <> 'P'
Solution
a month ago
Contributor
Posts: 62

Re: Need to filter a condition in Proc sql

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 133 views
  • 0 likes
  • 4 in conversation