topic Re: How do I use the lag function based on key variables to sum over a period of time in SAS Programming

How do I use the lag function based on key variables to sum over a period of time

kingsii24 — Tue, 09 Jun 2020 00:46:56 GMT

In the above data set I've identified the previous (most recent) training an individual (represented by ID) prior to taking an IT training, see column labeled "Prev_Train". Now, I'm trying to figure out how to count the length of time an individual was enrolled in their previous training for, prior to enrolling in their IT training. For example, for ID 1, prior to enrolling in IT training, they were enrolled in BT training for 0.285714 months. See below for what the final table should look like:

Re: How do I use the lag function based on key variables to sum over a period of time

ed_sas_member — Tue, 09 Jun 2020 07:29:58 GMT

Hi @kingsii24

Here is an attempt to achieve this.

I just wondered why sum_rev_training is equal to 3 and not 4 for ID=3 ? Is it a mistake?

Best,

data have;
	infile datalines dlm=" " dsd missover;
	input ID Training_T $ Date_start:MMDDYY10. Date_end:MMDDYY10. Month_fr Prev_train $;
	format Date_start Date_end MMDDYY10.;
	datalines;
1 IT 1/1/2019 1/31/2019 1
1 BT 2/1/2019 2/8/2019 0.285714
1 IT 2/9/2019 2/28/2019 0.714286 BT
2 BT 11/1/2018 11/11/2018 0.366667
2 IT 11/12/2018 11/30/2018 0.633333 BT
2 IIT 12/1/2018 12/31/2018 1
2 BT 1/1/2019 1/31/2019 1
2 IIT 2/1/2019 2/28/2019 1
3 IT 10/1/2018 10/31/2018 1
3 IT 11/1/2018 11/30/2018 1 IT
3 IT 1/1/2019 1/31/2019 1 IT
3 IT 2/1/2019 2/28/2019 1 IT
3 IT 12/1/2019 12/31/2019 1 IT
4 IIT 9/1/2018 9/30/2018 1
4 BT 10/1/2018 10/14/2018 0.451613
4 IIT 10/15/2018 10/31/2018 0.548387
4 IIT 11/1/2018 11/2/2018 0.066667
4 BT 11/3/2018 11/30/2018 0.933333
;
run;

data want;
	set have;
	by ID Training_T notsorted;
	_lag = lag(Month_fr);
	if not missing(Prev_train) then _Sum_rev_training + _lag;
	else call missing(_Sum_rev_training);
	if last.Training_T then Sum_rev_training = _Sum_rev_training;
	drop _:;
run;

Re: How do I use the lag function based on key variables to sum over a period of time

MCoopmans — Tue, 09 Jun 2020 08:05:54 GMT

Hello,

Not sure if I'm understanding you logic well, but I think that using BY-groups for your ID variable , and adding some if-the-else statements could get you pretty far.

data have;
infile cards;
input ID Training_T : $3. Date_start : mmddyy10. Date_End : mmddyy10. ;
last_day= intnx('month',Date_Start,0,'end');
Months_F=(intck('day', Date_start, Date_End)+1) / day(last_day);

format Date_start date9. 
	   Date_End date9. 
	   last_day date9.;
drop last_day ;	   
cards;
1 IT   1/1/2019   1/31/2019  
1 BT   2/1/2019   2/08/2019 
1 IT   2/9/2019   2/28/2019
2 BT  11/1/2018  11/11/2018
2 IT  11/12/2018 11/30/2018
2 IIT 12/1/2018  12/31/2018
2 BT   1/1/2019   1/31/2019
2 IIT  2/1/2019   2/28/2019
3 IT  10/1/2018  10/31/2018
3 IT  11/1/2018  11/30/2018
3 IT   1/1/2019   1/31/2019
3 IT   2/1/2019   2/28/2019
3 IT  12/1/2019  12/31/2019
;
run;

and here is a data step with by groups:

data want;
 set have;
 by ID;
 if first.id then sum_temp=0;
 Prev_Training=LAG(Training_T);
 if training_t ne 'IT' then Prev_Training='';
 if Training_T ne 'IT' then sum_temp + Months_F;
 if Training_t = 'IT' then Sum_Prev_Training = sum_temp;
 drop sum_temp ;
run;

and this is the result:

as the ID "3" has only taken IT training, it is not clear how you want to process that data ?

Greetings,

Mathias.