topic Re: Sub count required as per the below data in SAS Programming

Sub count required as per the below data

Mastanvali — Tue, 21 Nov 2023 11:13:11 GMT

Hi Team,

I have the data like below:

Subject	FolderName	FolderSeq	VSTEMP	VSTPT	count
101	Screening	1.0	37.1	Supine	1
101	Day 1	2.0	36.9	Supine	1
101	Day 8	5.0	36.8	Supine	1
101	Day 15	6.0	37.1	Supine	1
101	Day 22	7.0	36.7	Supine	1
101	Day 29	8.0	36.9	Supine	1
101	Day 36	9.0	36.9	Supine	2
101	Day 43 / ET	10.0	36.8	Supine	1
101	Safety Follow-Up	12.0	36.6	Supine	1
102	Screening	1.0	36.8	Supine	1
102	Day 1	2.0	36.8	Supine	2
102	Day 8	5.0	36.8	Supine	3
102	Day 15	6.0	37.0	Supine	1
102	Day 22	7.0	37.1	Supine	1
102	Day 29	8.0	36.9	Supine	1
102	Day 36	9.0	37.0	Supine	1
102	Day 43 / ET	10.0	37.0	Supine	2
102	Safety Follow-Up	12.0	36.9	Supine	1

I am want like below, i tried m in data step but its not working.

Subject	FolderName	FolderSeq	VSTEMP	VSTPT	count	Sub_cnt
101	Screening	1.0	37.1	Supine	1	1
101	Day 1	2.0	36.9	Supine	1	1
101	Day 8	5.0	36.8	Supine	1	1
101	Day 15	6.0	37.1	Supine	1	1
101	Day 22	7.0	36.7	Supine	1	1
101	Day 29	8.0	36.9	Supine	1	2
101	Day 36	9.0	36.9	Supine	2	2
101	Day 43 / ET	10.0	36.8	Supine	1	1
101	Safety Follow-Up	12.0	36.6	Supine	1	1
102	Screening	1.0	36.8	Supine	1	3
102	Day 1	2.0	36.8	Supine	2	3
102	Day 8	5.0	36.8	Supine	3	3
102	Day 15	6.0	37.0	Supine	1	1
102	Day 22	7.0	37.1	Supine	1	1
102	Day 29	8.0	36.9	Supine	1	1
102	Day 36	9.0	37.0	Supine	1	2
102	Day 43 / ET	10.0	37.0	Supine	2	2
102	Safety Follow-Up	12.0	36.9	Supine	1	1

Re: Sub count required as per the below data

andreas_lds — Tue, 21 Nov 2023 11:35:58 GMT

Please post the data as data steps using datalines and describe the rules that want applied. If you have tried something, post the code you have, so that we can suggest corrections.

Re: Sub count required as per the below data

Mastanvali — Tue, 21 Nov 2023 11:58:37 GMT

Hi,

Data d;
infile datalines;
input Subject FolderName :$20. FolderSeq VSTEMP VSTPT:$10.;
dataline;
101 Screening 1.0 37.1 Supine
101 Day 1 2.0 36.9 Supine
101 Day 8 5.0 36.8 Supine
101 Day 15 6.0 37.1 Supine
101 Day 22 7.0 36.7 Supine
101 Day 29 8.0 36.9 Supine
101 Day 36 9.0 36.9 Supine
101 Day 43 / ET 10.0 36.8 Supine
101 Safety Follow-Up 12.0 36.6 Supine
102 Screening 1.0 36.8 Supine
102 Day 1 2.0 36.8 Supine
102 Day 8 5.0 36.8 Supine
102 Day 15 6.0 37.0 Supine
102 Day 22 7.0 37.1 Supine
102 Day 29 8.0 36.9 Supine
102 Day 36 9.0 37.0 Supine
102 Day 43 / ET 10.0 37.0 Supine
102 Safety Follow-Up 12.0 36.9 Supine
run;

I did the below logic:
proc sort data=d; by subject FolderSeq VSTEMP; run;

data d1;
set d;
by subject folderseq VSTEMP;
retain count;
if first.VSTEMP and first.subject then count=1;
if VSTEMP=lag(VSTEMP) and subject=lag(subject) then count=count+1;
else count=1;
run;
proc sort data=base2; by subject folderseq VSTEMP; run;

got the output like:

Subject FolderName FolderSeq VSTEMP VSTPT count
101 Screening 1.0 37.1 Supine 1
101 Day 1 2.0 36.9 Supine 1
101 Day 8 5.0 36.8 Supine 1
101 Day 15 6.0 37.1 Supine 1
101 Day 22 7.0 36.7 Supine 1
101 Day 29 8.0 36.9 Supine 1
101 Day 36 9.0 36.9 Supine 2
101 Day 43 / ET 10.0 36.8 Supine 1
101 Safety Follow-Up 12.0 36.6 Supine 1
102 Screening 1.0 36.8 Supine 1
102 Day 1 2.0 36.8 Supine 2
102 Day 8 5.0 36.8 Supine 3
102 Day 15 6.0 37.0 Supine 1
102 Day 22 7.0 37.1 Supine 1
102 Day 29 8.0 36.9 Supine 1
102 Day 36 9.0 37.0 Supine 1
102 Day 43 / ET 10.0 37.0 Supine 2
102 Safety Follow-Up 12.0 36.9 Supine 1
I tried to achieve below highlighted with yellow and also, i want data like
in highlighted yellow color.
Subject FolderName FolderSeq VSTEMP VSTPT count Sub_cnt
101 Screening 1.0 37.1 Supine 1 1
101 Day 1 2.0 36.9 Supine 1 1
101 Day 8 5.0 36.8 Supine 1 1
101 Day 15 6.0 37.1 Supine 1 1
101 Day 22 7.0 36.7 Supine 1 1
101 Day 29 8.0 36.9 Supine 1 2
101 Day 36 9.0 36.9 Supine 2 2
101 Day 43 / ET 10.0 36.8 Supine 1 1
101 Safety Follow-Up 12.0 36.6 Supine 1 1
102 Screening 1.0 36.8 Supine 1 3
102 Day 1 2.0 36.8 Supine 2 3
102 Day 8 5.0 36.8 Supine 3 3
102 Day 15 6.0 37.0 Supine 1 1
102 Day 22 7.0 37.1 Supine 1 1
102 Day 29 8.0 36.9 Supine 1 1
102 Day 36 9.0 37.0 Supine 1 2
102 Day 43 / ET 10.0 37.0 Supine 2 2
102 Safety Follow-Up 12.0 36.9 Supine 1 1

Re: Sub count required as per the below data

Mastanvali — Tue, 21 Nov 2023 12:00:47 GMT

Data d;

infile datalines;

input Subject FolderName :$20. FolderSeq VSTEMP VSTPT:$10.;

dataline;

101 Screening 1.0 37.1 Supine
101 Day 1 2.0 36.9 Supine
101 Day 8 5.0 36.8 Supine
101 Day 15 6.0 37.1 Supine
101 Day 22 7.0 36.7 Supine
101 Day 29 8.0 36.9 Supine
101 Day 36 9.0 36.9 Supine
101 Day 43 / ET 10.0 36.8 Supine
101 Safety Follow-Up 12.0 36.6 Supine
102 Screening 1.0 36.8 Supine
102 Day 1 2.0 36.8 Supine
102 Day 8 5.0 36.8 Supine
102 Day 15 6.0 37.0 Supine
102 Day 22 7.0 37.1 Supine
102 Day 29 8.0 36.9 Supine
102 Day 36 9.0 37.0 Supine
102 Day 43 / ET 10.0 37.0 Supine
102 Safety Follow-Up 12.0 36.9 Supine

run;

I did the below logic:

proc sort data=d; by subject FolderSeq VSTEMP; run;

data d1;
set d;
by subject folderseq VSTEMP;
retain count;
if first.VSTEMP and first.subject then count=1;
if VSTEMP=lag(VSTEMP) and subject=lag(subject) then count=count+1;
else count=1;
run;
proc sort data=base2; by subject folderseq VSTEMP; run;

got the output like:

Subject	FolderName	FolderSeq	VSTEMP	VSTPT	count
101	Screening	1.0	37.1	Supine	1
101	Day 1	2.0	36.9	Supine	1
101	Day 8	5.0	36.8	Supine	1
101	Day 15	6.0	37.1	Supine	1
101	Day 22	7.0	36.7	Supine	1
101	Day 29	8.0	36.9	Supine	1
101	Day 36	9.0	36.9	Supine	2
101	Day 43 / ET	10.0	36.8	Supine	1
101	Safety Follow-Up	12.0	36.6	Supine	1
102	Screening	1.0	36.8	Supine	1
102	Day 1	2.0	36.8	Supine	2
102	Day 8	5.0	36.8	Supine	3
102	Day 15	6.0	37.0	Supine	1
102	Day 22	7.0	37.1	Supine	1
102	Day 29	8.0	36.9	Supine	1
102	Day 36	9.0	37.0	Supine	1
102	Day 43 / ET	10.0	37.0	Supine	2
102	Safety Follow-Up	12.0	36.9	Supine	1

While implementing the above logic only I tried to achieve the below sub_cnt and also, i want data like in sub_cnt column.

Subject	FolderName	FolderSeq	VSTEMP	VSTPT	count	Sub_cnt
101	Screening	1.0	37.1	Supine	1	1
101	Day 1	2.0	36.9	Supine	1	1
101	Day 8	5.0	36.8	Supine	1	1
101	Day 15	6.0	37.1	Supine	1	1
101	Day 22	7.0	36.7	Supine	1	1
101	Day 29	8.0	36.9	Supine	1	2
101	Day 36	9.0	36.9	Supine	2	2
101	Day 43 / ET	10.0	36.8	Supine	1	1
101	Safety Follow-Up	12.0	36.6	Supine	1	1
102	Screening	1.0	36.8	Supine	1	3
102	Day 1	2.0	36.8	Supine	2	3
102	Day 8	5.0	36.8	Supine	3	3
102	Day 15	6.0	37.0	Supine	1	1
102	Day 22	7.0	37.1	Supine	1	1
102	Day 29	8.0	36.9	Supine	1	1
102	Day 36	9.0	37.0	Supine	1	2
102	Day 43 / ET	10.0	37.0	Supine	2	2
102	Safety Follow-Up	12.0	36.9	Supine	1	1

Re: Sub count required as per the below data

PaigeMiller — Tue, 21 Nov 2023 12:25:59 GMT

What is the logic here? Why is the value of sub_cnt equal to 2 for Day 29 subject 101?

Re: Sub count required as per the below data

Mastanvali — Tue, 21 Nov 2023 13:32:32 GMT

we need to flag as yes for 3 or more consecutive values. but in my logic if i have 2 consecutive values also it is flagging yes but i do not want flag as yes if i have only 2 consecutives.

Re: Sub count required as per the below data

PaigeMiller — Tue, 21 Nov 2023 13:38:21 GMT

@Mastanvali wrote:

we need to flag as yes for 3 or more consecutive values. but in my logic if i have 2 consecutive values also it is flagging yes but i do not want flag as yes if i have only 2 consecutives.

I don't know what this means. 3 or more consecutive values of what? Flag for what reason? No flag is mentioned or shown in your original post.

How does the value of sub_cnt equal to 2 for Day 29 subject 101? Please walk me through this step-by-step.