@hjjijkkl wrote:
I want the output that look like this
R45 9/10/19 70
R45 10/8/19 60
R30 9/7/19 50
R30 8/18/20 40
continues ..............................
............................
Do you want the observations having Visit < 80?
EDIT: If not, please explain for each observation listed why do you expect it in the result.
So much of this thread has been spent just trying to clarify what the task statement actually should be: "consecutive dates" vs "consecutive records", two less than 80, but not more than two. I've had to read a lot of answers to respondent questions to finally understand what the objective is.
I think your best option is to create an intermediate dataset with a dummy variable (_UNDER80=1 if visit<80). Then read that data set with a BY ID _UNDER80 NOTSORTED to count record-groups and output groups of size 2 if _UNDER80=1:
data have;
input ID :$3. Date :mmddyy. Visit ;
format date date9.;
datalines;
R45 3/15/19 90
R45 4/15/19 100
R45 5/15/19 70
R45 6/15/19 60
R90 3/15/19 90
R90 4/15/19 100
R90 5/15/19 120
R90 6/15/19 100
R30 3/15/19 90
R30 4/15/19 87
R30 5/15/19 50
R30 6/15/19 40
run;
data need;
set have;
_under80=(visit<80);
run;
data want (drop=_:);
do _count=1 by 1 until (last._under80);
set need;
by id _under80 notsorted;
end;
do until (last._under80);
set need;
by id _under80 notsorted;
if _count=2 and _under80=1 then output;
end;
run;
Edited addition:
There is a way to do this in a single step - i.e. avoiding the DATA NEED step. It requires the ability to compare whether the current visit and upcoming visit (_nxt_visit below) both have the same status when filtered as less-than 80.
data want (drop=_:);
do _count=1 by 1 until (last.id or (sign(79.5-visit) ^= sign(79.5-_nxt_visit)));
set have (keep=id);
by id notsorted;
merge have have(firstobs=2 keep=visit rename=(visit=_nxt_visit));
end;
do _i=1 to _count;
set have;
if _count=2 and visit<80 then output;
end;
run;
So now, instead of
DO _COUNT=1 BY 1 UNTIL (last._under80);
I have
DO _COUNT=1 BY 1 UNTIL (LAST.ID or (SIGN(79.5-VISIT)^-SIGN(79.5-NXT_VISIT)));
The loops both stop at the same record. Note I use SIGN(79.5-VISIT) for force the result to only be -1 or +1. If I had used SIGN(80-VISIT), then the sign could also be zero (for visit=80). I would have gotten the same output dataset, but the visit=80 records would be unnecessarily treated as their own group, And this tactic works fine as long as VISIT values are integers.
The real "trick" here is using the self merge with a "FIRSTOBS=2" option as a way to compare current and upcoming VISIT values.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.