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)
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;
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
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?
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.