I have data that's structured like this, with several IDs:
ID linker date diff flag
A aaa 000 30Jan2014 0 1
A aaa 000 28Feb2014 27 1
A aaa 000 29Mar2014 27 1
A aab 000 26Apr2014 0 2
A aab 000 31May2014 33 2
A aab 000 14Oct2014 134 2
A aac 000 09Jul2014 0 3
A aac 000 09Aug2014 29 3
A aac 000 15Nov2014 96 3
A aad 000 10Sep2014 0 4
Diff = (date-1) - (lag(date)+1). But, it should only be calculated for every second record of a different linker, since that's essentially another group within the ID. I've set up the flags as such indicating when the linker changes. However, the next criteria is that if any diff exceeds 62, it becomes its own groups and the ones following it (if they are under 62 become a futher group). So the new flag should be:
ID linker date diff flag
A aaa 000 30Jan2014 0 1
A aaa 000 28Feb2014 27 1
A aaa 000 29Mar2014 27 1
A aab 000 26Apr2014 0 2
A aab 000 31May2014 33 2
A aab 000 14Oct2014 134 3
A aac 000 09Jul2014 0 4
A aac 000 09Aug2014 29 4
A aac 000 15Nov2014 96 5
A aad 000 10Sep2014 0 6
data test2b; set test4plus; flag+0; do until (last.id); set test4plus; by id flag; if first.id=0 then do; if diff > 62 then flag+1; else flag+0; end; output; end; run;
This is the code I tried, but it doesn't accurately output what the flag should be.
Here is a solution:
data have;
informat id $1. linker $6. date date9.;
format id $1. linker $6. date date9.;
input ID$ linker$ date;
cards;
A aaa000 30Jan2014
A aaa000 28Feb2014
A aaa000 29Mar2014
A aab000 26Apr2014
A aab000 31May2014
A aab000 14Oct2014
A aac000 09Jul2014
A aac000 09Aug2014
A aac000 15Nov2014
A aad000 10Sep2014
;run;
data want;
set have;
by id linker;
Diff = (date-1) - (lag(date)+1);
if first.linker then diff=0;
if first.linker or diff>62 then flag+1;
run;
Here is a solution:
data have;
informat id $1. linker $6. date date9.;
format id $1. linker $6. date date9.;
input ID$ linker$ date;
cards;
A aaa000 30Jan2014
A aaa000 28Feb2014
A aaa000 29Mar2014
A aab000 26Apr2014
A aab000 31May2014
A aab000 14Oct2014
A aac000 09Jul2014
A aac000 09Aug2014
A aac000 15Nov2014
A aad000 10Sep2014
;run;
data want;
set have;
by id linker;
Diff = (date-1) - (lag(date)+1);
if first.linker then diff=0;
if first.linker or diff>62 then flag+1;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.