SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Counting across variables with stopping/repeating condition

Reply
New Contributor
Posts: 3

Counting across variables with stopping/repeating condition

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!

Frequent Contributor
Posts: 75

Re: Counting across variables with stopping/repeating condition

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

New Contributor
Posts: 3

Re: Counting across variables with stopping/repeating condition

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:

 

output-sas.PNG

Super User
Posts: 13,941

Re: Counting across variables with stopping/repeating condition

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;

or

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.

PROC Star
Posts: 1,400

Re: Counting across variables with stopping/repeating condition

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;
PROC Star
Posts: 629

Re: Counting across variables with stopping/repeating condition

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;
Thanks,
Suryakiran
New Contributor
Posts: 3

Re: Counting across variables with stopping/repeating condition

Posted in reply to SuryaKiran
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?
Super User
Posts: 13,941

Re: Counting across variables with stopping/repeating condition


@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.

Ask a Question
Discussion stats
  • 7 replies
  • 106 views
  • 2 likes
  • 5 in conversation