topic Find date difference by multiple groups in SAS Procedures

Find date difference by multiple groups

deep3 — Thu, 23 Sep 2021 12:41:14 GMT

Hi,

I have dataset as below. For each id, i am trying to find days difference between the last activity reported and first sale.

activitytype id date datediff

activity 1 01-Jan-20

activity 1 08-Jan-20

sale 1 01-Feb-20 24

activity 2 01-Jan-20

sale 2 05-Jan-20 4

activity 2 08-Jan-20

sale 2 10-Feb-20 33

Initially i tried spliting the data into activity to get last.activity by id and sale to get first.sale by id. but this is leaving out row 6 and row 7. code I tried is below. Please suggest

/*sale dataset */

by id;
if first.id then flag = 2;

/*activity dataset */

by id;
if last.id then flag = 1;

Re: Find date difference by multiple groups

PaigeMiller — Thu, 23 Sep 2021 12:47:13 GMT

UNTESTED CODE

data want;
    set have;
    prev_id=lag(id);
    prev_activitytype=lag(activitytype);
    prev_date=lag(date);
    if id=prev_id and activitytype='sale' and prev_activitytype='activity'
        then datediff=date-prev_date;
    drop prev_:;
run;

If you want tested code, you would need to provide your data as actual SAS data step code, following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Re: Find date difference by multiple groups

Ksharp — Thu, 23 Sep 2021 13:19:55 GMT

data have;

set have;

if lag(activitytype)='sale' then group+1;

run;

proc sql;
create table want as
select * , range(date) as date_diff
from have
group by group;
quit;