topic Re: format based on more than 1 variable in New SAS User

format based on more than 1 variable

PierreYvesILY — Thu, 23 Apr 2020 14:24:31 GMT

dear SAS experts,

how can I simplify such blocks:

- it's a new varaible based on more than 1 variable and using a formula

- I have many of these structures in many programs

Can I define a format to make it easier and how?

/* LAUFZEIT_CLUSTER_HJ */
(CASE
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 6
THEN 6
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 12
THEN 12
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 18
THEN 18
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 24
THEN 24
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 30
THEN 30
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 36
THEN 36
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 42
THEN 42
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 48
THEN 48
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 54
THEN 54
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 60
THEN 60
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 66
THEN 66
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 72
THEN 72
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 78
THEN 78
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 84
THEN 84
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 90
THEN 90
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 96
THEN 96
WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 120
THEN 120
ELSE 10000
END) FORMAT=BESTX12. AS LAUFZEIT_CLUSTER_HJ,

Regards,

Re: format based on more than 1 variable

Astounding — Thu, 23 Apr 2020 15:03:50 GMT

Except for the last two categories, you should be able to collapse the logic to:

WHEN FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) <= 96
THEN 

ceil(FLOOR(DATDIF(t1.VERTRBGN, t1.DTENDFLG, '30/360')/30) / 6) * 6

Re: format based on more than 1 variable

Tom — Thu, 23 Apr 2020 15:17:17 GMT

I don't understand the question. You aren't using a format now. And the logic you are using now is NOT using more than one variable. It is just testing the result of the FLOOR(DATADIF()) result.

The problem you will have is that you want as output a NUMBER and a FORMAT always returns TEXT. You can use an INFORMAT to convert TEXT to numbers, but your input is a number, not text. It might just be easier to create a FORMAT and pass the value INPUT() function to convert the text the format generates to a number.

So in a data step you could run this assignment statement.

LAUFZEIT_CLUSTER_HJ = input(put(FLOOR(DATDIF(....)),MYFORMAT.),32.);

Re: format based on more than 1 variable

PierreYvesILY — Thu, 23 Apr 2020 15:37:01 GMT

I use 2 variables: VERTRBGN and DTENDFLG, in a function. It's using the difference between those 2 dates to build a cluster : for instance, if DTENDFLG - VERTRBGN < 12, the cluster is labelled '12'.

My question is how to create the format that would give the same result as the code that I posted. My result can be in text or in character, it's not important for this step.

Re: format based on more than 1 variable

ballardw — Thu, 23 Apr 2020 16:18:40 GMT

Formats always apply to a single variable. If you want the calculation done then that is a function and you pass the result of the function to a format.

A format, is basically a look up table that takes a single value and compares it to individual values or a range of values to return the value label indicated for the range.

Re: format based on more than 1 variable

Tom — Thu, 23 Apr 2020 16:54:30 GMT

Do you want the FLOOR() or not? The difference is which category does 6.5 get displayed as? 6 or 12?

Does the DATDIF() even return non-integer values?

If you want 6.5 to display as 6 then set the upper bounds on the ranges to 7,13, etc and exclude the upper bounds.

Example:

proc format ;
value myformat 
low -< 7 = '6'
  7 -< 13 = '12'
 13 -< 19 = '18'
 19 -< 25 = '24'
 25 -< 31 = '30'
 31 -< 37 = '36'
 37 -< 43 = '42'
 43 -< 49 = '48'
 49 -< 55 = '54'
 55 -< 61 = '68'
 61 -< 67 = '66'
 67 -< 73 = '72'
 73 -< 79 = '78'
 79 -< 85 = '84'
 85 -< 91 = '90'
 91 -< 97 = '96'
 97 -< 121= '120'
121 - high = '10000'
 other = 'Missing'
 ;
run;

data test;
 do i=6 to 18 by 6,96,120;
  put i= @ ;   
   do x=i-0.5,i,i+0.5,i+1;

     result=put(x,myformat.);
     output;
  end;
  put;
end;
run;
proc print;
  by i ;
  id i;
run;

  i      x      result

  6      5.5    6
         6.0    6
         6.5    6
         7.0    12


 12     11.5    12
        12.0    12
        12.5    12
        13.0    18


 18     17.5    18
        18.0    18
        18.5    18
        19.0    24


 96     95.5    96
        96.0    96
        96.5    96
        97.0    120


120    119.5    120
       120.0    120
       120.5    120
       121.0    10000