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?
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.