topic Re: How to generate cumulative count in one block in New SAS User

How to generate cumulative count in one block

Ivy — Thu, 25 Jul 2019 18:22:51 GMT

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

Re: How to generate cumulative count in one block

novinosrin — Thu, 25 Jul 2019 18:17:45 GMT

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;

Re: How to generate cumulative count in one block

Shmuel — Thu, 25 Jul 2019 18:15:51 GMT

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;

Re: How to generate cumulative count in one block

Ivy — Thu, 25 Jul 2019 18:49:55 GMT

Thank you. That works at my listed data. However, I missed one condition, at first.ID, flag ='check' . So , if we use: first.id then count_check = not missing(flag) will be more thoughtful. Thanks again.

Re: How to generate cumulative count in one block

Ivy — Thu, 25 Jul 2019 18:50:03 GMT

Thank you , that works. Is there any way that I can use that cumulative number as the lag function ?

Re: How to generate cumulative count in one block

Shmuel — Thu, 25 Jul 2019 19:02:12 GMT

@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;

Re: How to generate cumulative count in one block

novinosrin — Thu, 25 Jul 2019 19:08:50 GMT


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;