Output depending on a CASE WHEN condition

[Newsas2023]

Hi Team,

 Looking to satisfy the below condition and get an output accordingly. Please see below. 

data data.my_data;

input my_number;

datalines;

80.000000001

1000.01

879.987

70.987656

13.097360083

;

Run;

New my_number1 column should be satisfying the below conditions:

1. my_number1 should be rounded to 2 decimal places

2. my_number1 should be rounded to 2nd decimal if the my_number has any non zero value in the 3rd to 9th decimal places. Example: If my_number is 80.000000001, round up to 80.01

3. If my_number is greater than 999.99 then my_number1 is set to 999.99

[PaigeMiller]

Please show us what you have tried so far.

[Newsas2023]

Hi @PaigeMiller, I was trying the below, but haven't succeeded. Trying different combinations like splitting the numbers and then performing a check

PROC SQL;
CREATE TABLE data.new_dataset AS
SELECT t1.*,

CASE WHEN substr(scan(put(my_number,best32.),2,"."),1,1) = '0' AND ....
THEN ...

WHEN LTV_New > 999.9
THEN 999.9

ELSE round(LTV_New, 0.01)
END AS new_num

FROM data.my_data t1;
QUIT;

[Patrick]

Not sure what type of rounding that is. Does below return the desired result? If not then please provide the desired result based on your sample data. What should happen if there is a non-zero value on the 10th or later decimal?

data have;
  input my_number;
  datalines;
80.000000001
1000.01
879.987
70.987656
13.097360083
.
;

data want;
  set have;
  format my_number derived_num 16.9;
  if not missing(my_number) then
    derived_num=min(999.99,ceilz(my_number*100)/100);
run;

proc print data=want;
run;

[Newsas2023]

Hi @Patrick , this is great. Thank you very much for this. Will try and incorporate this into my code.
Would it be possible to round the "derived_num" column to 2 decimal places?
Example: if I use the round function on column "derived_num" to give me 2 decimal places after the decimal point, then Obs 5 would be 13.1, is it possible to get 13.10?

[Tom]

There is no difference between 13.1 and 13.10.  They are the same number.

If you want the value to always be displayed using 2 decimal places then attach a format that 2 for the number of decimal places.

format derived_num 8.2 ;

[Newsas2023]

Thanks a lot, @Tom, will try this out. True, they are the same number, this was from an output requirement standpoint. Appreciate it!