BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ambadi007
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
AlanC
Barite | Level 11

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. 

https://github.com/savian-net

View solution in original post

7 REPLIES 7
Reeza
Super User

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

ambadi007
Quartz | Level 8
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
Super User

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
Quartz | Level 8

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

LinusH
Tourmaline | Level 20
By the loonof the eye this aboukd work. The p isn't in lowcase...?
Data never sleeps
ambadi007
Quartz | Level 8

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
Barite | Level 11

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. 

https://github.com/savian-net

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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