topic Counting Consecutive 0s in SAS Programming

Counting Consecutive 0s

kac — Thu, 25 Jun 2009 16:14:31 GMT

Hello,

I am trying to tackle a problem in SAS. I have a dataset that has 1 row per ID. All of the rest of the variables are coded with a 0 or 1. What I am trying to do is flag if a member has 31 or more consecutive 0s. However, I only want to count the 0s that are in between each member’s first 1 and last 1.

Example #1: ID #1234’s first 1 is the variable Mar0900 and their last 1 is the variable Apr0900. This person does not have 31+ consecutive 0s in the variables between Mar0900 and Apr0900. Flag=N

Example #2: ID #2222’s first 1 is the variable Mar1000 and their last 1 is the variable Apr1500. This person has 31+ consecutive 0s in the variables between Mar1000 and Apr1500. Flag=Y

Example #3: ID #9876’s first 1 is the variable Apr0900 and their last 1 is the variable Apr1400. This person does not have 31+ consecutive 0s in the variables between Apr0900 and Apr1400. They do have 31+ consecutive 0s, but they happen before their first 1, therefore they do not count. Flag=N

Know of any code that I can use to do this? I would greatly appreciate any help in tackling this problem!

Thank you!

Re: Counting Consecutive 0s

sbb — Thu, 25 Jun 2009 16:30:48 GMT

Using a DATA step, setup an ARRAY declaring your variable list for the array contents. Using a DO/END loop and the DIM() function, work through the array variables and detect (using a temp SAS variable) your initial condition, then start counting (within the DO/END) until you reach the end of the array variable list -- you likely do not need to pass the variable list multiple times to establish the lowest/first and highest/last "1" condition/value, but that would depend on your DO/END loop code.

I suggest searching the SAS support website http://support.sas.com/ with some focus on SAS-hosted documentation and also supplemental technical/conference papers on ARRAY processing, and use DIM as a search keyword.

Scott Barry
SBBWorks, Inc.

Re: Counting Consecutive 0s

deleted_user — Thu, 25 Jun 2009 22:09:34 GMT

As Scott mentioned you can acheive this by using arrays and Do loop. I have tried this for count >=4 then flag="Y". Try the below code if it fits for your requirement.

data test;
input id x1 x2 x3 x4 x5 x6 x7 x8 x9 x10;
cards;
101 1 0 0 0 0 0 1 0 0 0
102 0 0 0 0 0 0 1 0 0 0
103 1 0 0 0 0 0 0 0 0 0
104 1 0 0 1 0 0 0 0 0 1
105 1 0 0 1 0 0 0 0 1 0
;
run;

data test1;
set test;
by id notsorted;
array temp{*} x1-x10;
if first.id then do;
ss=0;
cnt=1;
end;
do i=1 to dim(temp)-1;
if temp(i)=1 then ss=1;
else if temp(i)=0 and ss=1 then do;
if temp(i+1)=1 then do;
cnt=cnt;
if cnt>=4 then do;
flag="Y";
cnt=1;
ss=0;
end;
end;
else if temp(i+1)=0 then cnt+1;
end;
end;
if flag='' then flag='N';
put _all_;
run;

~ Sukanya E

Re: Counting Consecutive 0s

ChrisNZ — Fri, 26 Jun 2009 01:02:33 GMT

Reusing Sukanya's data, you can also check this using character functions.
[pre]
data TEST;
input ID X1-X10;
STR=cats(of X1-X10); * create one string;
STR1=substr(STR,1,findc(STR,'1',-99)); * remove all zeroes after last 1;
FLAG=ifc(find(STR1,'0000',findc(STR1,'1')),'Y','N'); * look for 4 zeroes after 1st 1;
cards;
101 1 0 0 0 0 0 1 0 0 0
102 0 0 0 0 0 0 1 0 0 0
103 1 0 0 0 0 0 0 0 0 0
104 1 0 0 1 0 0 0 0 0 1
105 1 0 0 1 0 0 0 0 1 0
run;