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 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;
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/
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: