topic Re: Null values on bands in SAS Programming

Null values on bands

Solly7 — Tue, 24 Aug 2021 08:33:36 GMT

Hi, i need help in allocating score bands to the corresponding save_ratio.. i tried the below code but it returns null values 'dot' in the output "Saves_Ratio_Score'

data retentions;
  input Save_Ratio user : $20. Band_Indicator $;
  datalines;
0.45 ALUPIYA I
0.37 DLOUIS I
0.6 DMORAKE I
0.30 FDANISA O
0.50 FFARAO O
;

proc format;
   invalue inbound_ratio_band
      low  -< 0.35   = 0
      0.35 -< 0.43 = 0.08
      0.43 -< 0.473   = 0.16
      0.473 -< 0.52 = 0.24
      0.52 -< 0.59  = 0.32
      0.59 - high = 0.4
   ;
run;

proc format;
   invalue outbound_ratio_band
      low  -< 0.34   = 0
      0.34 -< 0.41 = 0.08
      0.41 -< 0.45   = 0.16
      0.45 -< 0.5 = 0.24
      0.5  -< 0.56 = 0.32
      0.56  - high = 0.4
   ;
run;

data fix_bands;
    SET retentions;
	IF	UPCASE(Band_Indicator)	=	'I'	THEN
		Saves_Ratio_Score	= INPUT(Save_Ratio,Inbound_ratio_band.);
	ELSE
	IF	UPCASE(Band_Indicator)	=	'O'	THEN
		Saves_Ratio_Score	= INPUT(Save_Ratio,Outbound_ratio_band.);
	CALL	MISSING(Saves_Ratio_Score);
run;

Re: Null values on bands

Solly7 — Tue, 24 Aug 2021 08:37:11 GMT

I added ELSE after the last THEN..but it still dont work

data fix_bands;
SET retentions;
IF UPCASE(Band_Indicator) = 'I' THEN
Saves_Ratio_Score = INPUT(Save_Ratio,Inbound_ratio_band.);
ELSE
IF UPCASE(Band_Indicator) = 'O' THEN
Saves_Ratio_Score = INPUT(Save_Ratio,Outbound_ratio_band.);
ELSE
CALL MISSING(Saves_Ratio_Score);
run;

Re: Null values on bands

Patrick — Tue, 24 Aug 2021 08:44:57 GMT

You first assign a value to Saves_Ratio_Score but then before writing the data to the target table you execute always:

CALL MISSING(Saves_Ratio_Score);

This sets the variable always to missing. Just remove this statement and things should work for you as expected.

Re: Null values on bands

Solly7 — Tue, 24 Aug 2021 08:55:07 GMT

Thanks a lot Patrick..your solutions works!