Simple Percent Format Question

[JediApprentice]

I have values in a dataset that I want to turn into percents. The problem is, when I apply the percent format, it multiplies the values by 100. I don't want to do this because the values are already multiplied by 100, they simply don't have a percent sign. They look like this: 19.5, 30, 5, 20. So, I'm trying to accomplish this within PROC SQL for the variables brand_discount and generic_discount as such toward the bottom of the query (with the percent format):

PROC SQL;
  CREATE TABLE COMMERCIAL_CLAIMS AS
  SELECT t1.ApprovedPriceType,
         t1.AWP_Rate,
		 (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=PERCENT3.2,
		 t2.Generic_Discount FORMAT=PERCENT3.2,
		 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;

Instead of getting the percents I want, I'm getting values like 1500%, 3000%... Any tips on how I can get Brand_Discount and Generic_Discount into the proper format?

[Reeza · Accepted solution]

Create a custom format and then use that:

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

And then apply that:

proc sql;
...
t2.brand_discount format=mypct.,
t2.generic_discount format=mypct.
...
quit;

This is a common problem in proc tabulate

http://support.sas.com/kb/38/001.html

[BrunoMueller]

Hi

Just in case, I would change the format, so that it also cares for negative percentage values, see sample code below.

proc format;
  picture mypcta
    low - high ='000,009.00%'
  ;
  picture mypctb (round)
    low -< 0 ='000,009.00%' (prefix="-")
    0 - high = '000,009.00%' 
  ;
run;

data have;
  do i = 1 to 20;
    value = (rannor(0) * 100);
    pcta = value;
    pctb = value;
    realPct = value / 100;
    output;
  end;

  format
    value comma10.4
    pcta mypcta.
    pctb mypctb.
    realPct percentn9.2
  ;
run;

Bruno