topic Creating buckets in Case When Statement in SAS Programming

Creating buckets in Case When Statement

tobyfarms — Wed, 04 Oct 2017 19:54:17 GMT

I'm trying to create a simple buckets in a Proc Sql and its not bucketing the groups correctly. No Errors, simply not adding the remainder in the 3rd bucket.

This appears to work correctly if I eliminate the 3rd case statement.

Select

firmware_ID, State, County, Zip, Miles_Avg,

Case when Miles_Avg <=20 then Miles_Avg 20 else bucket1,

Case when Miles_Avg <=100 then (Miles_Avg -bucket1) else 0 end as bucket2,

Case when Miles_Avg <=250 then (Miles_Avg -(bucket1+bucke2)) else 0 end as bucket3

from metrics2017

order by count;

Re: Creating buckets in Case When Statement

ballardw — Wed, 04 Oct 2017 19:57:03 GMT

Likely spelling:

Case when Miles_Avg <=250 then (Miles_Avg -(bucket1+bucke2)) else 0 end as bucket3

Though for a large number of cases a simple format applied to the variable is more flexible than creating multiple variables.

Re: Creating buckets in Case When Statement

Reeza — Wed, 04 Oct 2017 19:59:20 GMT

What does your log say? Why are there no END statements for the first CASE statement?

Re: Creating buckets in Case When Statement

HB — Wed, 04 Oct 2017 21:51:07 GMT

If my Miles_Avg is 10, doesn't that equal the condition for all three case statements?

Re: Creating buckets in Case When Statement

ShiroAmada — Thu, 05 Oct 2017 01:30:03 GMT

I tried this

Case when Miles_Avg <=20 then Miles_Avg 20 else bucket1,

it did not work for me.

Miles_Avg 20 - will this work?

No end in your case too.

Re: Creating buckets in Case When Statement

ChrisNZ — Thu, 05 Oct 2017 04:00:09 GMT

What error messages do you get?

This code would generate an error such as

ERROR: The following columns were not found in the contributing tables: bucket1

since the variable does not exist. You need this

Case when Miles_Avg <=100 then (Miles_Avg - calculated bucket1) else 0 end as bucket2

Re: Creating buckets in Case When Statement

tobyfarms — Thu, 05 Oct 2017 11:51:32 GMT

Thanks @ChrisNZ, I never got an error message. The results would populate, just incorrectly. Would I need to add the calculated function for the 'bucket3' string as well?

Re: Creating buckets in Case When Statement

ballardw — Thu, 05 Oct 2017 15:39:53 GMT

@tobyfarms wrote:

Thanks @ChrisNZ, I never got an error message. The results would populate, just incorrectly. Would I need to add the calculated function for the 'bucket3' string as well?

It always helps to provide a few rows of example input data and the output for that data.

Re: Creating buckets in Case When Statement

Reeza — Thu, 05 Oct 2017 15:48:27 GMT


		Select
			firmware_ID, State, County, Zip, Miles_Avg,
		Case 
			when Miles_Avg <=20 then Miles_Avg 
			else 0 
		end as bucket1,
		Case 
			when Miles_Avg <=100 then (Miles_Avg - CALCULATED bucket1) 
			else 0 
		end as bucket2,
		Case 
			when Miles_Avg <=250 then (Miles_Avg -(CALCULATED bucket1 + CALCULATED bucket2)) 
			else 0 
		end as bucket3
		from metrics2017
			order by count;

I think this may be what you're looking for... or at least closer.

Note the following:

1. Use the CALCULATED keyword before a variable you're using that's created in the same query

2. All CASES should have an END AS <variable name>

3. You have some syntax errors (miles_avg 20) -> 20 is what? extra space there?

3b. bucke2 should be bucket2 most likely

4. You likely have some logic issues, but since you didn't state your logic and your code is wrong I'm not sure we can make suggestions.

Please review the guidelines here on how to ask a question. This is a simple question and you could have had an answer significantly earlier if you follow the guidelines: https://stackoverflow.com/help/how-to-ask