BookmarkSubscribeRSS Feed
tobyfarms
Fluorite | Level 6

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;

 

8 REPLIES 8
ballardw
Super User

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.

Reeza
Super User

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

 

 

HB
Barite | Level 11 HB
Barite | Level 11

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

 

ShiroAmada
Lapis Lazuli | Level 10

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.

ChrisNZ
Tourmaline | Level 20

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

tobyfarms
Fluorite | Level 6

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?

ballardw
Super User

@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.

Reeza
Super User

		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

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
  • 8 replies
  • 2608 views
  • 0 likes
  • 6 in conversation