topic Re: How to count sequential values by observation in New SAS User

How to count sequential values by observation

aeb — Wed, 25 Sep 2019 22:47:54 GMT

Hi all,

I have a dataset of laboratory results. Labs were collected at multiple time points. I am specifically interested in knowing if a patient tested negative at sequential time points. Specifically, if they tested negative twice or more in a row. Here's an example dataset, where neg1-neg5 is a yes/no (1/0) for a negative value. I do not want to reorder the dataset, as it is important to keep the results in the order in which they were received.

This is an example of what I have:

patid	neg1	neg2	neg3	neg4	neg5
1	0	1	0	0	1
2	1	1	1	0	0
3	0	1	1	0	0
4	0	1	1	1	1
5	0	0	0	1	0

And I would like something like this, where #seqneg is the number of sequential negatives by patient.

patid	neg1	neg2	neg3	neg4	neg5	#seqneg
1	0	1	0	0	1	0
2	1	1	1	0	0	3
3	0	1	1	0	0	2
4	0	1	1	1	1	4
5	0	0	0	1	0	0

Thank you!!

Re: How to count sequential values by observation

Reeza — Wed, 25 Sep 2019 23:05:52 GMT

What about this case?

patid neg1 neg2 neg3 neg4 neg5
6 1 1 0 1 1

Re: How to count sequential values by observation

aeb — Wed, 25 Sep 2019 23:24:32 GMT

Hi Reeza,

You are actually anticipating my next step. At this point I'm only interested in any occurrence of two or more - but I will eventually want to know if they were interrupted by a positive result.

I should also note, I have up to 10 results per patient (most patients do not have 10).

Thank you!

Re: How to count sequential values by observation

aeb — Wed, 25 Sep 2019 23:40:22 GMT

And to specifically answer the question in your scenario, I would want #seqneg to be 2.

Re: How to count sequential values by observation

novinosrin — Thu, 26 Sep 2019 00:17:34 GMT

Hi @aeb @Reeza 's intuitive reasoning is brilliant, so the following is solely for your sample. Not asking you to rely on this , but my share of fun


data have;
infile cards truncover;
input patid	neg1	neg2	neg3	neg4	neg5;
cards;
1	0	1	0	0	1
2	1	1	1	0	0
3	0	1	1	0	0
4	0	1	1	1	1
5	0	0	0	1	0
;

data want;
set have;
_k=cats(of neg:);
_f=findc(_k,'1');
_l=findc(_k,'1','b');
want= (countc(_k,'1')=_l-_f+1)*countc(_k,'1')*(_l-_f>0) ;
drop _:;
run;

Re: How to count sequential values by observation

mkeintz — Thu, 26 Sep 2019 03:18:46 GMT

You apparently want the length of the longest sequence of '1', as long as that length is at least 2.

You could start by converting your sequence of numbers to a string (e.g. 0 1 1 1 0 becomes '01110'). You could then iteratively search STRNG for substrings '11111', '1111', '111', '11', '1', '' until you have success. Such a program would look like this:

data have;
infile cards truncover;
input patid	neg1	neg2	neg3	neg4	neg5;
cards;
1	0	1	0	0	1
2	1	1	1	0	0
3	0	1	1	0	0
4	0	1	1	1	1
5	0	0	0	1	0
;

data want (drop=strng);
  set have;
  length strng $5;
  strng=cats(of neg:);

  do seqneg=5 to 1 by -1 until(find(strng,repeat('1',seqneg-1)));
  end;
  if  seqneg=1 then seqneg=0;
run;

The "do seqneg=5 to 1 by -1 until (.....) stops when either the UNTIL condition resolves to a non-zero, or when seqneg reaches 1. The until is

until(find(strng,repeat('1',seqneg-1)));

which says search STRNG for the character "1" with SEQNEG-1 repetitions. If the FIND is unsuccessful, the function returns a zero, but otherwise returns the position in which the substring is found.

For a set of 10 NEG variables just change the length of STRNG to $10 and start the do loop at 10 instead of 5.

If this looks a little complicated then you could change the code to:

data want2 (drop=strng substrng found);
  set have;
  length strng $5  substrng $5;
  strng=cats(of neg:);
  do seqneg=5 to 1 by -1 until(found^=0);
    substrng=repeat('1',seqneg-1);
    found=find(strng,trim(substrng));
  end;
  if  seqneg=1 then seqneg=0;
run;

which utilizes more intermediate variables.

regards,

Mark

Re: How to count sequential values by observation

aeb — Thu, 26 Sep 2019 03:57:20 GMT

Mark and novinosrin:

Thank you both, very much.

novinosrin - you were right, code worked in specific scenarios but not all.

Mark - worked and I appreciate the explanation.

Thanks again!

Re: How to count sequential values by observation

PGStats — Thu, 26 Sep 2019 21:40:00 GMT

If you want to stick with numbers and auto adjust to any number of neg values:

data have;
infile cards truncover;
input patid neg1    neg2    neg3    neg4    neg5;
cards;
1   0   1   0   0   1
2   1   1   1   0   0
3   0   1   1   0   0
4   0   1   1   1   1
5   0   0   0   1   0
;

data want;
set have;
array a dum1 neg: dum2;
seq = 0;
do i = 2 to dim(a);
	if a{i} and not a{i-1} then start = i;
	else if a{i-1} and not a{i} then do;
		if i - 1 > start then
			if i - start > seq then seq = i - start;
		end;
	end;
drop dum1 dum2 i start;
run;

Variables dum1 and dum2 pad the array and allow us to skip testing end conditions. /p>

Re: How to count sequential values by observation

Haris — Thu, 26 Sep 2019 16:19:07 GMT

An approach with transposition:

data c;
input PID neg1 neg2 neg3 neg4 neg5;
cards;
1 0 1 0 0 1
2 1 1 1 0 0
3 0 1 1 0 0
4 0 1 1 1 1
5 0 0 0 1 0
6 1 1 0 1 1
;
proc transpose data=c out=ct; var Neg: ; by PID;
run;

data cc; set ct; by PID;
	if first.PID=1									then count=0; else;
	if first.PID NE 1 AND Col1=1 AND lag(Col1)=1	then Count+1; else;
	if Col1=0										then Count=0;
run;

proc sql;
	select PID, max(Count) as Count
	from CC
	group by PID
;
quit;

If you skip the last line `IF COL1=0` it will continue the count: e.g., PID=6 will have the count of two rather than one.

Re: How to count sequential values by observation

RichardDeVen — Thu, 26 Sep 2019 17:51:26 GMT

Thinking outside the box, you can coerce an input statement to read a concatenation of the flag variables transformed to appear like an csv input line. Lots of tricks mashed into this answer:

DATA Step with SET and INFILE
Dummy DATALINES for INPUT to introduce _INFILE_
INFILE CARDS with TRUNCOVER to prevent LOST CARD
Held input with @@ to prevent dataline advancement
INPUT @1 to reposition parse start point
INPUT with (one:) variable list specifier to read into an array
INPUT with (binary32.:) informat list to read 1's as binary data upto next delimiter (,) using : list input modifier
MAX of one: to determine largest run of 1's
LOG2 of 1+MAX to get number of 1's in largest value that must be 2^p - 1

* faux data with 32 neg flag variables;

data have;
  do patid = 1 to 20;
    array neg neg1-neg32;
    do over neg;
      neg = ranuni(123) < 0.35;
    end;
    output;
  end;
run;

data want;
  attrib longest_run length=8;

  set have;
  infile cards dlm=',' truncover;

  if _n_ = 1 then input @1 @@; * activate _infile_;

  _infile_ = translate(cats(of neg:),',','0'); * repurpose _infile_ value;
  * put _infile_;

  array one one1-one32;
  input @1 (one:) (binary32.:) @@;  * read the _infile_ (input buffer);
  _error_ = 0;

  longest_run = log2 (1+max(of one:));

  drop one:;
cards;
.
run;