topic Re: 3 days lag in SAS Programming

3 days lag

Emma8 — Tue, 03 Nov 2020 12:48:31 GMT

I have data with daily date data. I would like to identify 3 days interval (Day_3), see below:

Date Day_3

01012020 1

01022020 1

01032020 1

01042020 2

01052020 2

01062020 2

etc.

Thank you.

Re: 3 days lag

PeterClemmensen — Tue, 03 Nov 2020 12:52:58 GMT

If your data is daily, then it is as simple as

data have;
input Date :mmddyy8.;
format date mmddyy10.;
datalines;
01012020
01022020
01032020
01042020
01052020
01062020
;

data want;
   set have;
   if mod(_N_, 3) = 1 then day_3 + 1;
run;

Re: 3 days lag

Kurt_Bremser — Tue, 03 Nov 2020 13:18:07 GMT

And if you need to do this for some kind of groups, see here:

data want;
set have;
by group;
if first.group
then do;
  day_3 = 1;
  count = 0;
end;
else do;
  count + 1;
  if mod(count,3) = 0 then day_3 + 1;
end;
drop count;
run;

Re: 3 days lag

Emma8 — Tue, 03 Nov 2020 13:35:16 GMT

Sorry, my actual date does not consequentially increase and also duplicate dates, so when skipped then still would like to count for the lag, for example, see below (between a start and end date--below start is 01012020 and end is 01182020):
datalines Day_3
01012020 1
01062020 2
01062020 2
01062020 2
01102020 4
01102020 4
01182020 6
;

Re: 3 days lag

PeterClemmensen — Tue, 03 Nov 2020 13:40:58 GMT

I don't follow this logic. Why is Day_3 equal o 4 and 6 in the last few obs?

Re: 3 days lag

Kurt_Bremser — Tue, 03 Nov 2020 13:44:03 GMT

Because it's in the 4th or 6th 3-day group from the beginning.

@PeterClemmensen wrote:

I don't follow this logic. Why is Day_3 equal o 4 and 6 in the last few obs?

Re: 3 days lag

Emma8 — Tue, 03 Nov 2020 13:44:35 GMT

By the actual calendar date

Re: 3 days lag

Kurt_Bremser — Tue, 03 Nov 2020 13:52:16 GMT

Expanding from my previous post:

data have;
input group $ date :mmddyy8.;
format date yymmdd10.;
datalines;
A 01012020
A 01062020
A 01062020
A 01062020
A 01102020
A 01102020
A 01182020
;

data want;
set have;
by group;
retain first;
if first.group
then first = date;
day_3 = int((date-first)/3)+1;
drop first;
run;

If you don't have groups, do the init at _N_ = 1 instead of first.group.

Re: 3 days lag

Emma8 — Tue, 03 Nov 2020 13:47:20 GMT

It should count the actual calendar date not by _n_

Re: 3 days lag

Emma8 — Tue, 03 Nov 2020 13:50:20 GMT

Although the actual date from 2 January 2020 to 6 January 2020 etc. skipped in the data, I want to keep track from the beginning date to the end date

Re: 3 days lag

Kurt_Bremser — Tue, 03 Nov 2020 13:50:59 GMT

@Emma8 wrote:
It should count the actual calendar date not by _n_

Sorry for that, edited my post.

Sometimes the behavior of SAS Studio and Safari with regards to copy/pasting drives me crazy.