Dear all,
Please see the data below.
I would like to generate a column name as 'countcheck' to count the continuous "check" status in the ID block.
Also based on this cumulative number to generate new lag start date.
I am wondering how to generate it.
Thank you very much.
Ivy
PatientID | line number | Start date | end date | flag1 | countcheck | start date new |
a | 1 | x1 | x | |||
a | 2 | x2 | x | |||
a | 3 | x3 | x | |||
a | 4 | x4 | x | |||
a | 5 | x5 | x | check | 1 | lag1(start date) = x4 |
a | 6 | x6 | x | check | 2 | lag2(start date) = x4 |
a | 7 | x7 | x | |||
a | 8 | x8 | x | check | 1 | lag1(start date) = x7 |
a | 9 | x9 | x | check | 2 | lag2(start date) = x7 |
b | 1 | y1 | x | |||
b | 2 | y2 | x | |||
b | 3 | y3 | x | |||
b | 4 | y4 | x | check | 1 | lag1(start date) = y3 |
b | 5 | y5 | x | |||
b | 6 | y6 | x | check | 1 | lag1(start date) = y5 |
b | 7 | y7 | x | check | 2 | lag2(start date) = y5 |
b | 8 | y8 | x | check | 3 | lag3(start date) = y5 |
data have;
infile cards truncover;
input (PatientID line_number Startdate enddate flag1) ($);
cards;
a 1 x1 x
a 2 x2 x
a 3 x3 x
a 4 x4 x
a 5 x5 x check 1 lag1(start date) = x4
a 6 x6 x check 2 lag2(start date) = x4
a 7 x7 x
a 8 x8 x check 1 lag1(start date) = x7
a 9 x9 x check 2 lag2(start date) = x7
b 1 y1 x
b 2 y2 x
b 3 y3 x
b 4 y4 x check 1 lag1(start date) = y3
b 5 y5 x
b 6 y6 x check 1 lag1(start date) = y5
b 7 y7 x check 2 lag2(start date) = y5
b 8 y8 x check 3 lag3(start date) = y5
;
data want;
set have;
by PatientID;
if first.PatientID then call missing(count);
k=flag1='check';
if k then Count+1;
else call missing(count);
retain start_date_new ' ';
start_date_new=ifc(count=1,lag(startdate),start_date_new);
if missing( flag1) then call missing(start_date_new);
drop k;
run;
data have;
infile cards truncover;
input ID $ Line_Number flag $ ;
cards;
a 1
a 2
a 3
a 4
a 5 check 1
a 6 check 2
a 7
a 8 check 1
a 9 check 2
b 1
b 2
b 3
b 4 check 1
b 5
b 6 check 1
b 7 check 2
b 8 check 3
;
data want;
set have;
by id;
if first.id then call missing(count);
k=flag='check';
if k then Count+1;
else call missing(count);
drop k;
run;
@Ivy wrote:
Thank you , that works. Is there any way that I can use that cumulative number as the lag function ?
You can add next lines:
retain start_date_new lag_date;
lag_date = start_date; drop lag_date;
...
if missing(flag) then call missing(start_new_date);
if count_check=1 then start_date_new = lag_date;
data have;
infile cards truncover;
input (PatientID line_number Startdate enddate flag1) ($);
cards;
a 1 x1 x
a 2 x2 x
a 3 x3 x
a 4 x4 x
a 5 x5 x check 1 lag1(start date) = x4
a 6 x6 x check 2 lag2(start date) = x4
a 7 x7 x
a 8 x8 x check 1 lag1(start date) = x7
a 9 x9 x check 2 lag2(start date) = x7
b 1 y1 x
b 2 y2 x
b 3 y3 x
b 4 y4 x check 1 lag1(start date) = y3
b 5 y5 x
b 6 y6 x check 1 lag1(start date) = y5
b 7 y7 x check 2 lag2(start date) = y5
b 8 y8 x check 3 lag3(start date) = y5
;
data want;
set have;
by PatientID;
if first.PatientID then call missing(count);
k=flag1='check';
if k then Count+1;
else call missing(count);
retain start_date_new ' ';
start_date_new=ifc(count=1,lag(startdate),start_date_new);
if missing( flag1) then call missing(start_date_new);
drop k;
run;
1) Please edit your post your data using the "running-man" icon in the form
data have;
input ID $1. line_number flag $5. ;
datalines;
a 1
... your data ...
;
run;
2) You can use next code to count "check" status:
options missing = ' ' ;
data want;
set have;
by id; /* assuming data is sorted by ID */
retain count_check;
if first.ID then count_check = .;
if flag = 'check' then count_check +1;
run;
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 25. 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.