calculated delay between dates with condition

[Nasser_DRMCP]

data have;
  input client $ date1 date9. event1 $
    date2 date9. event2 $
    date3 date9. event3 $
    imp1: date9. imp2: date9. imp3: date9. ;
  format date: imp: date9. ;
datalines ;
12345 01JAN2020 CREATION 01APR2020 CREATION 01JUL2020 PROLONGATION 01MAR2020 01MAY2020 01SEP2020
;

Hello,

happy new year !

For each imp, I would like to calculate the duration between the imp date and the greatest event among the creation event

thanks a lot in advance

delay1 of the imp1 = 2 = from january to mars (event 1 is the begining)

delay2 of the imp2 = 1 = from april to may (event 2 is the begining)

delay3 of the imp3 = 5 = from april to september (event 2 is the begining, event3 is ignored because not creation)

[ballardw]

What the heck is a "greatest event"?

You can get differences between date values by either subtracting, which gives days as a result, or the INTCK function to get counts of other types of intervals.

Since Imp1 is a date it is very unlikely to every be 2 as in

delay1 of the imp1 = 2 = from january to mars (event 1 is the begining)

so you need to provide a bit more description of your problem and show what the actual result of the data given should be.

BTW, best is not to have blank lines in DATALINES block

[Tom]

It might be easier if your data was more normalized. Perhaps something like this?

data have ;
  input client event :$20. date :date. ;
  format date date9.;
cards;
12345 CREATION       01JAN2020
12345 CREATION       01APR2020
12345 PROLONGATION   01JUL2020
12345 IMPLEMENTATION 01MAR2020
12345 IMPLEMENTATION 01MAY2020
12345 IMPLEMENTATION 01SEP2020
;

So given data in this form what output would you want?

[s_lassen · Accepted solution]

Here is a way to get what you want:

data want;
  set have;
  array delays(*) 8 delay1-delay3;
  array dates(*) 8 date1-date3;
  array events(*) $8 event1-event3;
  array imps(*) 8 imp1-imp3;
  do _N_=1 to dim(dates);
    if events(_N_)='CREATION' then
      _created=dates(_N_);
    delays(_N_)=intck('month',_created,imps(_N_));
    end;
  drop _created;
run;

[Kurt_Bremser]

The first thing you need to do is put your data into a usable structure, or avoid the transposition to wide if that happened further up in your process.

Search for "double transpose" here, or with Google.