CASE WHEN calculation returning missing value

[JediApprentice]

I have the following query:

proc format;                           
   picture mypct low-high='000,009.00%';   
run;

/*****************************************************************************************************************/

PROC SQL;
  CREATE TABLE COMMERCIAL_CLAIMS_PREP AS
  SELECT t1.ApprovedPriceType,
         t1.AWP,
         t1.AWP_Rate,
		 t1.BRAND_GENERIC,
		 (t1.AWP*t1.ApprovedDispensingFeeAmount) FORMAT=DOLLAR16.2 AS Current_Pricing,
         t1.CarrierID, 
         t1.CLIENT,
         t1.FilledMonth, 
         t1.'Filled Quarter'n, 
         t1.GPINumber, 
         t1.MailOrderIndicator,
         t1.Lives, 
         t1.MultiSourceCode, 
         t1.PriceScheduleDefinition, 
         t1.RXNetwork,
         t1.SuperPharmacyNetworkID, 
         t1.SUM_OF_ApprovedIngredientCost, 
         t1.SUM_OF_AWP, 
         t1.SUM_OF_RX, 
         t1.SUM_OF_QUANTITYDISPENSED,
         t1.'AFF Name'n, 
         t1.'AFF Type'n, 
         t1.NETWORK, 
         t1.Business_Line, 
         t1.Analytics_Grouping1, 
         t1.MACSavingsGrouping,
         t1.'MAC List'n,
		 t1.Segment,
		 t1.IVLIndicator,
		 t1.Fund_Type,
		 t2.Brand_Discount FORMAT=mypct.,
		 t2.Generic_Discount FORMAT=mypct.,
		 t2.Brand_Dispense_Fee,
		 t2.Generic_Dispense_Fee
  FROM EGTASK.All_Combined_Table t1 LEFT JOIN EGTASK.PTSLCT t2 ON (input(t1.SERVICEPROVIDERID,8.) = t2.NCPDP)
  WHERE Business_Line = 'Commercial';
QUIT;

PROC SQL;
  CREATE TABLE COMMERCIAL_CLAIMS AS
  SELECT t1.ApprovedPriceType,
         t1.AWP,
         t1.AWP_Rate,
		 t1.BRAND_GENERIC,
		 t1.Current_Pricing,
         t1.Lives, 
         t1.NETWORK, 
         t1.Business_Line, 
		 t1.Segment,
		 t1.Brand_Discount,
		 t1.Generic_Discount,
		 t1.Brand_Dispense_Fee,
		 t1.Generic_Dispense_Fee,
		 (CASE
		    WHEN BRAND_GENERIC='BRAND' AND ApprovedPriceType='AWP' THEN (t1.AWP-(t1.AWP*t1.Brand_Discount))
		  END) AS Brand_AWP_Repriced,
		 (CASE
			WHEN BRAND_GENERIC='GENERIC' AND substr(t1.ApprovedPriceType,1,3) ^= 'MAC' THEN (t1.AWP-(t1.AWP*t1.Generic_Discount))
		  END) AS Generic_AWP_Repriced
  FROM COMMERCIAL_CLAIMS_PREP t1;
QUIT;

In the second sub-query, I'm trying to reprice claims based on Average Wholesale Price (AWP) and Brand_Discount (which is in a custom percentage format). However, when I try to calculate the Brand_AWP_Repriced in the CASE WHEN, it returns a missing value. Could this be because of the custom format? How could I get around this?

[ballardw · Accepted solution]

The format should not affect it as the non-formatted value is used in calculations.

What kind of values do you have for AWP and Brand_Discount when you have BRAND_GENERIC='BRAND' AND ApprovedPriceType='AWP' (make sure of case)

[JediApprentice]

Aha, you were right. I did not pay close enough attention to the case. BRAND_GENERIC has values of either 'Brand' or 'Generic'. Sometimes it's the simplest of things that gets me. Thank you for your help.

[ballardw]

There are reasons casual observers of my code often find lots of UPCASE function calls when I deal with character variables and comparisons. ; )