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
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.
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.