Score bands

[Solly7]

Hi, I need help in allocating score bands to outbound and inbound users based on their QA_Score from retentions table, please note that the last two users FDANISA and FFARAO need to be allocated only outbound_save_band values and first 3 users needs to be only allocated the inbound_save_band value. 

data retentions;
  input QA_score user $20.;
  datalines;
114 ALUPIYA 
130 DLOUIS 
150 DMORAKE 
100 FDANISA 
122 FFARAO   
;

proc format;
invalue inbound_save_band
low -< 114 = 0
114 -< 137 = 0.08
137 -< 152 = 0.16
152 -< 167 = 0.24
167 -< 190 = 0.32
190 - high = 0.4
;
run;

proc format;
invalue outbound_save_band
low -< 82 = 0
82 -< 98 = 0.08
98 -< 109 = 0.16
109 -< 120 = 0.24
120 -< 136 = 0.32
136 - high = 0.4
;
run;

data want

QA_Score         user            inbound_outbound_score

114                ALUPIYA            0

130               DLOUIS              0.08

150               DMORAKE         0.16

100               FDANISA            0.16

122               FFARAO             0.32

[jimbarbour · Accepted solution]

For something like this, you'll need to put some kind of flag or indicator in the data which can be used to determine whether inbound or outbound will apply.

I would adjust your code as follows:

data retentions;
  input QA_score user : $20. Band_Indicator $;
  datalines;
114 ALUPIYA I
130 DLOUIS I
150 DMORAKE I
100 FDANISA O
122 FFARAO O
;
RUN;

proc format;
invalue inbound_save_band
low -< 114 = 0
114 -< 137 = 0.08
137 -< 152 = 0.16
152 -< 167 = 0.24
167 -< 190 = 0.32
190 - high = 0.4
;
run;

proc format;
invalue outbound_save_band
low -< 82 = 0
82 -< 98 = 0.08
98 -< 109 = 0.16
109 -< 120 = 0.24
120 -< 136 = 0.32
136 - high = 0.4
;
run;

DATA	Want;
	SET	Retentions;
	IF	UPCASE(Band_Indicator)	=	'I'	THEN
		Inbound_Outbound_Score	=	INPUT(PUT(QA_Score, 3.), Inbound_Save_Band3.);
	ELSE
	IF	UPCASE(Band_Indicator)	=	'O'	THEN
		Inbound_Outbound_Score	=	INPUT(PUT(QA_Score, 3.), Outbound_Save_Band3.);
	ELSE
		CALL	MISSING(Inbound_Outbound_Score);
RUN;

Results:

Jim

[Reeza]

Slight modification on @jimbarbour solution to use INPUTN.

DATA	Want;
	SET	Retentions;
	length fmt $32.;
	IF	UPCASE(Band_Indicator)	=	'I'	THEN fmt = "Inbound_save_band";
	ELSE IF	UPCASE(Band_Indicator)	=	'O'	THEN fmt = "outbound_save_band";
       *any other cases you need to deal with?;
       ELSE ....;
   
		
    Inbound_Outbound_Score	=	INPUTN(PUT(QA_Score, 3.), fmt);

RUN;

[Solly7]

Thanks a lot Reeza!

[Solly7]

Thanks a lot Jim...you really saved me!!