Using two columns to group another table

[beccab]

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

[PeterClemmensen]

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 · Accepted solution]

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]

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 

[PeterClemmensen]

Makes sense. Anytime. Glad you found your answer 🙂