topic Re: Counting across variables with stopping/repeating condition in SAS Data Management

Counting across variables with stopping/repeating condition

hjones_6 — Fri, 13 Jul 2018 14:56:41 GMT

Hi guys, I have a data set that is just a sample for my bigger one. The code looks like this:

data counting_test;
input id a b c d e;
datalines;
12 0 1 0 0 0
13 0 0 1 0 0
14 1 0 0 0 0
;
run;

Essentially this will be a duration counter that will count across the variables 'a' through 'e' so that it will check how long it takes for the binary variable to be a 1. I currently can't get any code to work or output anything that is close to what I need. Any help would be greatly appreciated!

Re: Counting across variables with stopping/repeating condition

Andygray — Fri, 13 Jul 2018 15:00:42 GMT

What is your expected output for the data you provided? You need to explain that piece along with your logic too

Re: Counting across variables with stopping/repeating condition

hjones_6 — Fri, 13 Jul 2018 15:12:59 GMT

sorry about that! I have been trying to complete a do loop that will accomplish this using the code below:

data counting_test_2;
set counting_test;
by id;
if first.id then do;
counter = 1;
end;
if last.id then do;
	if a = 0 then counter+1;
	if b = 0 then counter+1;
	if c = 0 then counter+1;
	if d = 0 then counter+1;
	if e = 0 then counter+1;
	end;
else do;
	if a = 1 then counter = 1;
	if b = 1 then counter = 2;
	if c = 1 then counter = 3;
	if d = 1 then counter = 4;
	if e = 1 then counter = 5;
end;
run;

Right now it is outputting the counter at 5 every time for every record. But my expected outcome is going to be:

Re: Counting across variables with stopping/repeating condition

ballardw — Fri, 13 Jul 2018 15:15:24 GMT

Perhaps Something like:

data counting_test;
input id a b c d e;
result= whichn(1,a,b,c,d,e);
datalines;
12 0 1 0 0 0
13 0 0 1 0 0
14 1 0 0 0 0
;
run;

If you have a largish number of variables an array may be easier to use, especially if they are either named with a numeric suffix (a1 to a25 for example) or adjacent in the data set.

The change in code would be to add an array definition:

array test a1-a25;

array test a -- lastvar;

and then use

result =whichn(1, of test(*));

the WHICHN function returns the first variable or value in a list that matches the value in the first position (which could be a variable itself). The "of test(*)" is one way to use all of the variable in the array.

Re: Counting across variables with stopping/repeating condition

PeterClemmensen — Fri, 13 Jul 2018 15:15:30 GMT

I'm not sure, but I think you want to do something like this

data counting_test;
input id a b c d e;
datalines;
12 0 1 0 0 0
13 0 0 1 0 0
14 1 0 0 0 0
;
run;

data want;
   set counting_test;
   array MyArray a b c d e;
   FirstOne=whichn(1, of MyArray[*]);
run;

Re: Counting across variables with stopping/repeating condition

SuryaKiran — Fri, 13 Jul 2018 15:20:30 GMT

Use Arrays

data counting_test;
input id a b c d e;
datalines;
12 0 1 0 0 0
13 0 0 1 0 0
14 1 0 0 0 0
;
run;

data want(drop=i);
set counting_test;
array num(*) _numeric_;
do i=1 to dim(num);
if num(i)=1 then Place=i-1;
end;
run;

Re: Counting across variables with stopping/repeating condition

hjones_6 — Fri, 13 Jul 2018 15:23:36 GMT

Thank you! One more quick question; is there anyway to put a repeat condition in this? like for it to restart if there are more than one 1's in the same record?

Re: Counting across variables with stopping/repeating condition

ballardw — Fri, 13 Jul 2018 15:43:24 GMT

@hjones_6 wrote:
Thank you! One more quick question; is there anyway to put a repeat condition in this? like for it to restart if there are more than one 1's in the same record?

Now you have to show what the output data set might look like. Multiple results now imply that you potentially have to record as many elements as you search, which means another array to hold things.

Better might be to explain HOW you will use the resulting multiple indicators.