DATA Step, Macro, Functions and more

Creating buckets in Case When Statement

Reply
Contributor
Posts: 44

Creating buckets in Case When Statement

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;

 

Super User
Posts: 11,343

Re: Creating buckets in Case When Statement

Posted in reply to tobyfarms

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.

Super User
Posts: 19,855

Re: Creating buckets in Case When Statement

Posted in reply to tobyfarms

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

 

 

Regular Contributor
Regular Contributor
Posts: 162

Re: Creating buckets in Case When Statement

Posted in reply to tobyfarms

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

 

Frequent Contributor
Posts: 100

Re: Creating buckets in Case When Statement

Posted in reply to tobyfarms

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.

PROC Star
Posts: 1,760

Re: Creating buckets in Case When Statement

Posted in reply to tobyfarms

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

Contributor
Posts: 44

Re: Creating buckets in Case When Statement

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?

Super User
Posts: 11,343

Re: Creating buckets in Case When Statement

Posted in reply to tobyfarms

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.

Super User
Posts: 19,855

Re: Creating buckets in Case When Statement

[ Edited ]
Posted in reply to tobyfarms

		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

Ask a Question
Discussion stats
  • 8 replies
  • 151 views
  • 0 likes
  • 6 in conversation