BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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)

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Pyrite | Level 9

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
Super User

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 995 views
  • 3 likes
  • 2 in conversation