BookmarkSubscribeRSS Feed
PierreYvesILY
Pyrite | Level 9

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,

PY

5 REPLIES 5
Astounding
PROC Star

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
Tom
Super User Tom
Super User

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.);
PierreYvesILY
Pyrite | Level 9

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.

ballardw
Super User

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.

Tom
Super User Tom
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 895 views
  • 2 likes
  • 4 in conversation