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

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

 

View solution in original post

2 REPLIES 2
Reeza
Super User

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
SAS Super FREQ

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

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
  • 2 replies
  • 5160 views
  • 3 likes
  • 3 in conversation