Hi, I can only apologise for what is probably about to be a stupid question! I have had a bit of a break from using SAS due to maternity leave and can't clear my head.
I've used a macro to bucket a variable. The output is a table, which has two columns, min_score and max_score, and a row for each bucket:
min_score max_score
0 1
2 2
3 3
4 4
5 6
7 27
I'd now like to apply that onto the dataset.
For example I have the VALUE column and I want to apply a character variable called BUCKET based on the min and max values. I want it to look like:
VALUE BUCKET
1 0-1
5 5-6
7 7-27
9 7-27
5 5-6
6 5-6
3 3
where bucket is character. Is this possible? Am I making my life too complicated? Thanks for any input (be kind with a sleep deprived mummy please!)
If your first table is a SAS data set, you can also automate the creation of the format like this
data have1;
input min_score max_score;
datalines;
0 1
2 2
3 3
4 4
5 6
7 27
;
data fmt;
length label $ 50;
set have(rename=(min_score=start max_score=end)) end=lr;
retain fmtname 'range' type 'n';
label=catx('-', start, end);
output;
if lr then do;
hlo='O';
label='Unknown';
output;
end;
run;
proc format library=work cntlin=fmt;
run;
And then use the same logic as before to create the desired output
data have2;
input value;
datalines;
1
5
7
9
5
6
3
;
data want;
set have2;
bucket=put(value, range.);
run;
Hi @beccab and welcome to the SAS Community (and back from maternity leave) 🙂
Use this as a template
proc format;
value range 0-1 = '0-1'
2 = '2'
3 = '3'
4 = '4'
5-6 = '5-6'
7-27 = '2-27'
;
run;
data have;
input value;
datalines;
1
5
7
9
5
6
3
;
data want;
set have;
bucket=put(value, range.);
run;
Result:
VALUE BUCKET 1 0-1 5 5-6 7 2-27 9 2-27 5 5-6 6 5-6 3 3
Feel free to ask if you have questions
If your first table is a SAS data set, you can also automate the creation of the format like this
data have1;
input min_score max_score;
datalines;
0 1
2 2
3 3
4 4
5 6
7 27
;
data fmt;
length label $ 50;
set have(rename=(min_score=start max_score=end)) end=lr;
retain fmtname 'range' type 'n';
label=catx('-', start, end);
output;
if lr then do;
hlo='O';
label='Unknown';
output;
end;
run;
proc format library=work cntlin=fmt;
run;
And then use the same logic as before to create the desired output
data have2;
input value;
datalines;
1
5
7
9
5
6
3
;
data want;
set have2;
bucket=put(value, range.);
run;
Thanks so much! I would probably (possibly) have got there eventually, but that helped get me going after picking up my old code and stumbling at the first hurdle! I should have said I'm going to be looping through and bucketing hundreds of columns, so the automated version is what I was after. Thanks for quick response
Makes sense. Anytime. Glad you found your answer 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.