BookmarkSubscribeRSS Feed
hjones_6
Calcite | Level 5

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!

7 REPLIES 7
Andygray
Quartz | Level 8

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

hjones_6
Calcite | Level 5

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

ballardw
Super User

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.

PeterClemmensen
Tourmaline | Level 20

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;
SuryaKiran
Meteorite | Level 14

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
hjones_6
Calcite | Level 5
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?
ballardw
Super User

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

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 899 views
  • 2 likes
  • 5 in conversation