Grouping Results by Value Ranges

[hnb_matt_d]

I have code that, via the intck function, calculates the number of days between to dates. I'm trying to group those results into range buckets using 1 - 5 days, 6 - 10, days, 11 - 15 days, 16 - 20 days, 21 - 30 days, and 30+ days. I still want the underlying data (each observation having a value in the 'SLA_AGING' field, but then an indicator of which bucket into which it would fall. At this time I'm exporting to Excel and manually creating the buckets. THANKS in advance for any and all assistance! Matt D

[ballardw · Accepted solution]

It might help to show some of the code you are currently using. It is extremely likely that this very easy to do do right after you use INTCK.

The question becomes which method: a block of If/then/else with ranges, a Select block or a format and whether you actually want a new variable you just to display the number of days with the bucket value.

BTW, where would zero or missing value for days fall? Or is that impossible in your data?

A format is the most flexible in general. Something like:

proc format;
value daybucket
1 - 5 = " 1 to 5 days"
6 - 10= " 6 to 10 days"
11- 15= "11 to 15 days"
16- 20= "16 to 20 days"
21- 25= "21 to 25 days"
26- 30= "26 to 30 days"
30-high="30+ days"
;
run;

Then associate your variable that has the number of days with the format when needed.

Proc freq data=yourdataset;
   tables sla_aging;
   format sla_aging daybucket.  ;
run;

You could display the value by the bucket text with any of the report procedures. Groups can be made using the format that will be honored in reporting, analysis and graphing procedures.

The format approach is a very powerful tool because you do not need to create additional variables to change groups for analysis, just change the format.

[hnb_matt_d]

I can't share exact code since it's pulling out of our case management system.  Below is very close to what I'm running in the third step.  The first 2 processes are the creation of the case_assigned table and the case_closed table.  I'm good with those two processes, just trying to get the SLA_AGING field grouped into buckets.  HOPE THIS HELPS! 

proc sql;
create table XX_SLA_AGING as
select case_assigned.case_identifier
,case_closed.full_name
,case_assigned.assigned_date
,intck('days',case_assigned.assigned_date,case_closed.closed_date) as SLA_AGING

from work.case_assigned
,work.case_closed

where case_assigned.case_identifier = case_closed.case_identifier

order by case_assigned.case_identifier;

quit;

[ballardw]

Use PUT with the calculated value and the format I provided to create a new variable if you want an additional variable.

Since you have created an output data set you could use the format assigned to bucket variable as I showed in proc freq or almost any other procedure.

[hnb_matt_d]

Exactly what I was seeking.  Thank you! 

[DavePrinsloo]

Create a range formats using PROC FORMAT. Then create a second column to store the bucket.

[ed_sas_member]

Hi @hnb_matt_d 

• Option using formats

You can first define a format with the various ranges, and then create a derived variable based on the original one with the format using the PUT() function:

proc format;
value daybucket
	 1- 5= "1 to 5 days"
	 6-10= "6 to 10 days"
	11-15= "11 to 15 days"
	16-20= "16 to 20 days"
	21-25= "21 to 25 days"
	26-30= "26 to 30 days"
	30<-high="30+ days" /*exclude 30 in the boundary using "<"*/
;
run;

data want;
	set have;
	SLA_AGING_CAT = put(SLA_AGING,daybucket.);
run;

• option using IF/THEN statements (less convenient to handle as value are hardcoded)

data want;
	set have;
	length SLA_AGING_CAT $ 100;
	if 1 <= SLA_AGING <= 5 then SLA_AGING_CAT = "1 to 5 days";
	else if 6 <= SLA_AGING <= 10 then SLA_AGING_CAT = "6 to 10 days";
	else if 11 <= SLA_AGING <= 15 then SLA_AGING_CAT = "11 to 15 days";
	else if 16 <= SLA_AGING <= 20 then SLA_AGING_CAT = "16 to 20 days";
	else if 21 <= SLA_AGING <= 25 then SLA_AGING_CAT = "21 to 25 days";
	else if 26 <= SLA_AGING <= 30 then SLA_AGING_CAT = "26 to 30 days";
	else if 30 <SLA_AGING then SLA_AGING_CAT = "30+ days";
run;

You can also automatize the process:

data want;
	set have;
	length SLA_AGING_CAT $ 100;
	do i=0 to 5;
		if 5*i+1 <= SLA_AGING <= 5*i+5 then SLA_AGING_CAT = catx(" ",5*i+1,"to",5*i+5,"days");
	end;
	if 30 <SLA_AGING then SLA_AGING_CAT = "30+ days";
	drop i;
run;

Best,