BookmarkSubscribeRSS Feed
kac
Calcite | Level 5 kac
Calcite | Level 5
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!
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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
ChrisNZ
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1511 views
  • 0 likes
  • 4 in conversation