topic Re: Using two columns to group another table in SAS Programming

Using two columns to group another table

beccab — Tue, 17 Sep 2019 10:39:55 GMT

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

Re: Using two columns to group another table

PeterClemmensen — Tue, 17 Sep 2019 10:46:35 GMT

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

Re: Using two columns to group another table

PeterClemmensen — Tue, 17 Sep 2019 11:00:23 GMT

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;

Re: Using two columns to group another table

beccab — Tue, 17 Sep 2019 11:27:55 GMT

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

Re: Using two columns to group another table

PeterClemmensen — Tue, 17 Sep 2019 11:29:01 GMT

Makes sense. Anytime. Glad you found your answer 🙂