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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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