topic Re: Issue with my bands in SAS Enterprise Guide

Issue with my bands

Sean_OConnor — Wed, 20 Nov 2019 11:31:32 GMT

Folks,

I have a macro which I'm using to convert a value into a band however I'm running into problems. Can anyone see what is going wrong?

%macro bands (yr);

data banded_&yr;
format claimed_band_&yr unclaimed_band_&yr $100.;
set cohort_&yr;
if claimed_&yr=0 or claimed_&yr=. then claimed_band_&yr='Zero';
else if claimed_&yr >=1 and claimed_&yr <=25000  then claimed_band_&yr='1 to 25,000';
else if claimed_&yr >=25001 and claimed_&yr <=50000  then claimed_band_&yr='25,001 to 50,000';
else if claimed_&yr >=50001 and claimed_&yr <=750000  then claimed_band_&yr='50,001 to 75,000';
else if claimed_&yr >=75001 and claimed_&yr <=100000  then claimed_band_&yr='75,001 to 100,000';
else if claimed_&yr >=100001 and claimed_&yr <=200000  then claimed_band_&yr='100,001 to 200,000';
else if claimed_&yr >=200001 and claimed_&yr <=300000  then claimed_band_&yr='200,001 to 300,000';
else if claimed_&yr >=300001 and claimed_&yr <=400000  then claimed_band_&yr='300,001 to 400,000';
else if claimed_&yr >=400001 and claimed_&yr <=500000  then claimed_band_&yr='400,001 to 500,000';
else if claimed_&yr >=500001 and claimed_&yr <=600000  then claimed_band_&yr='500,001 to 600,000';
else if claimed_&yr >=600001 and claimed_&yr <=700000  then claimed_band_&yr='600,001 to 700,000';
else if claimed_&yr >=700001 and claimed_&yr <=800000  then claimed_band_&yr='700,001 to 800,000';
else if claimed_&yr >=800001 and claimed_&yr <=900000  then claimed_band_&yr='800,001 to 900,000';
else if claimed_&yr >=900001 and claimed_&yr <=1000000  then claimed_band_&yr='900,001 to 1,000,000';
else if claimed_&yr >=1000001 and claimed_&yr <=5000000  then claimed_band_&yr='1,000,001 to 5,000,000';
else if claimed_&yr >=5000001 and claimed_&yr <=10000000  then claimed_band_&yr='5,000,001 to 10,000,000';
else if claimed_&yr >10000000  then claimed_band_&yr='Greater than 10,000,000';
if unused_&yr=0 or unused_&yr=. then unclaimed_band_&yr='Zero';
else if unused_&yr >=1 and unused_&yr <=25000  then unclaimed_band_&yr='1 to 25,000';
else if unused_&yr >=25001 and unused_&yr <=50000  then unclaimed_band_&yr='25,001 to 50,000';
else if unused_&yr >=50001 and unused_&yr <=750000  then unclaimed_band_&yr='50,001 to 75,000';
else if unused_&yr >=75001 and unused_&yr <=100000  then unclaimed_band_&yr='75,001 to 100,000';
else if unused_&yr >=100001 and unused_&yr <=200000  then unclaimed_band_&yr='100,001 to 200,000';
else if unused_&yr >=200001 and unused_&yr <=300000  then unclaimed_band_&yr='200,001 to 300,000';
else if unused_&yr >=300001 and unused_&yr <=400000  then unclaimed_band_&yr='300,001 to 400,000';
else if unused_&yr >=400001 and unused_&yr <=500000  then unclaimed_band_&yr='400,001 to 500,000';
else if unused_&yr >=500001 and unused_&yr <=600000  then unclaimed_band_&yr='500,001 to 600,000';
else if unused_&yr >=600001 and unused_&yr <=700000  then unclaimed_band_&yr='600,001 to 700,000';
else if unused_&yr >=700001 and unused_&yr <=800000  then unclaimed_band_&yr='700,001 to 800,000';
else if unused_&yr >=800001 and unused_&yr <=900000  then unclaimed_band_&yr='800,001 to 900,000';
else if unused_&yr >=900001 and unused_&yr <=1000000  then unclaimed_band_&yr='900,001 to 1,000,000';
else if unused_&yr >=1000001 and unused_&yr <=5000000  then unclaimed_band_&yr='1,000,001 to 5,000,000';
else if unused_&yr >=5000001 and unused_&yr <=10000000  then unclaimed_band_&yr='5,000,001 to 10,000,000';
else if unused_&yr >10000000  then unclaimed_band_&yr='Greater than 10,000,000';
val=1;
run;

proc summary data=banded_&yr;
class claimed_band_&yr;
var val;
output out=test_&yr
sum=;
run;




%mend bands;
%bands (5);

Re: Issue with my bands

PeterClemmensen — Wed, 20 Nov 2019 11:33:15 GMT

Is something wrong when you run it? If so, please post your log.

Re: Issue with my bands

Sean_OConnor — Wed, 20 Nov 2019 11:36:29 GMT

When I look at the outputted dataset I see that some records are placed into the wrong band. So for instance a record where claimed_&year = 107,148 is being having a band written down as between 50,001 to 75,000

Re: Issue with my bands

PeterClemmensen — Wed, 20 Nov 2019 11:44:34 GMT

You have an extra 0 in this line

else if claimed_&yr >=50001 and claimed_&yr <=750000  then claimed_band_&yr='50,001 to 75,000';

This should be

else if claimed_&yr >=50001 and claimed_&yr <=75000  then claimed_band_&yr='50,001 to 75,000';

Re: Issue with my bands

Kurt_Bremser — Wed, 20 Nov 2019 11:56:01 GMT

DO NOT do this with such a massive if/then/else avalanche. Create formats instead and make your code maintainable.

And if you have certain fractional values (e.g. 25000.5), these will have no result.

Re: Issue with my bands

Kurt_Bremser — Wed, 20 Nov 2019 12:12:48 GMT

Just see this:

proc format;
value claimed_band
  low - 0 = 'Zero'
  0 <- 25000 = '1 to 25,000'
  25000 <- 50000 = '25,001 to 50,000'
  50000 <- 75000 = '50,001 to 75,000'
  75000 <- 100000 = '75,001 to 100,000'
  100000 <- 200000 = '100,001 to 200,000'
  200000 <- 300000 = '200,001 to 300,000'
  300000 <- 400000 = '300,001 to 400,000'
  400000 <- 500000 = '400,001 to 500,000'
  500000 <- 600000 = '500,001 to 600,000'
  600000 <- 700000 = '600,001 to 700,000'
  700000 <- 800000 = '700,001 to 800,000'
  800000 <- 900000 = '800,001 to 900,000'
  900000 <- 1000000 = '900,001 to 1,000,000'
  1000000 <- 5000000 = '1,000,001 to 5,000,000'
  5000000 <- 10000000 = '5,000,001 to 10,000,000'
  10000000 <- high = 'Greater than 10,000,000'
;
run;

This format will catch ALL values, regardless of fractions. It is easier to maintain than the if/then/else blocks, and reusable. Your code will then become

%macro bands(yr);

data banded_&yr;
length claimed_band_&yr unclaimed_band_&yr $100.;
set cohort_&yr;
claimed_band_&yr = put(claimed_&yr,claimed_band.);
unclaimed_band_&yr = put(unused_&yr,claimed_band.);
val = 1;
run;

proc summary data=banded_&yr;
class claimed_band_&yr;
var val;
output
  out=test_&yr
  sum=
;
run;

%mend bands;