BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
beccab
Calcite | Level 5

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!)

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

 

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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

PeterClemmensen
Tourmaline | Level 20

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;

 

beccab
Calcite | Level 5

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 Smiley Happy

PeterClemmensen
Tourmaline | Level 20

Makes sense. Anytime. Glad you found your answer 🙂

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 846 views
  • 0 likes
  • 2 in conversation