topic Output depending on a CASE WHEN condition in SAS Programming

Output depending on a CASE WHEN condition

Newsas2023 — Sat, 12 Nov 2022 21:00:26 GMT

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

Re: Output depending on a CASE WHEN condition

PaigeMiller — Sat, 12 Nov 2022 21:41:20 GMT

Please show us what you have tried so far.

Re: Output depending on a CASE WHEN condition

Patrick — Sat, 12 Nov 2022 23:32:53 GMT

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;

Re: Output depending on a CASE WHEN condition

Newsas2023 — Sun, 13 Nov 2022 00:20:58 GMT

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;

Re: Output depending on a CASE WHEN condition

Newsas2023 — Sun, 13 Nov 2022 00:29:51 GMT

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?

Re: Output depending on a CASE WHEN condition

Tom — Sun, 13 Nov 2022 00:36:45 GMT

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 ;

Re: Output depending on a CASE WHEN condition

Newsas2023 — Sun, 13 Nov 2022 00:44:44 GMT

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