Sum array values more efficiently

Reply
Super Contributor
Posts: 418

Sum array values more efficiently

Hello everyone. This question is related to my earlier question about arrays.

I have a series of Columns (say 20) of which there are the values of either Y or N. I would like to add a variable that sums all of the "Y" through all of the columns. Therefore if 5 of the column had a "Y" then count would be 5. If none of the columns had a Y the count would be zero.


I have the following code which works (I think) but is there a more efficient way of doing this? I don't like the idea of using the DO loop, and was curious if there was some kind of function that you could just plug the array name into and it would count the distinct times a string occurs.

CODE I HAVE:

data thisworksIthink;

set mydata;

array var1(*)  ColumnB-ColumnH;

count=0;

do p=1 to dim(var1);

if var1(p)="Y" then count=sum(count,1);

end;

run;

What I would like if possibe:

data thisworksIthink;

set mydata;

array var1(*)  ColumnB-ColumnH;

newvar=count(var1(*),"Y");

run;

Or

data thisworksIthink;

set mydata;

array var1(*)  ColumnB-ColumnH;

newvar=count(cats(of var1(*)),"Y");

run;

Thanks again!

Respected Advisor
Posts: 3,156

Re: Sum array values more efficiently

Posted in reply to Anotherdream

This may work:

data thisworksIthink;

set mydata;

newvar=count(cats(of ColumnB--ColumnH),"Y");

run;

This is close to your second solution, only to omit the array.

Haikuo

Super Contributor
Posts: 418

Re: Sum array values more efficiently

That actually doesn't work as it is how I tried my other section.  It seems to work when using general column names, however my column names have spaces in them.

SO I do Count(cats(of "FI Section 1"n--"FI Section 13"n),"Y");  and it throws the following error.

ERROR: Found ""FI Section 1"n" when expecting )

Not sure what why that doesn't seem to want to work.

Thanks!

Respected Advisor
Posts: 3,156

Re: Sum array values more efficiently

Posted in reply to Anotherdream

Don't know if OPTIONS VALIDVARNAME=ANY; helps, but that is what I always put into the place before using literals.

Haikuo

Super Contributor
Posts: 307

Re: Sum array values more efficiently

This works . . .

options validvarname=any;

/* create some sample data, columns include spaces */

data test ;
drop i ;
do i = 1 to 100 ;
  id = strip ( put ( i , 8. ) );
  if(100*ranuni(0)+i)<50 then "b b"n='Y';
  else "b b"n='';
  if(100*ranuni(0)+i)<50 then "c c"n='Y';
  else "c c"n='';
  if(100*ranuni(0)+i)<50 then "d d"n = 'Y';
  else "d d"n = '';
  output;
end;
run;

data sumacross ;
set test;
sum = count ( cats (of "b b"n -- "d d"n), "Y");
run;

Super Contributor
Posts: 418

Re: Sum array values more efficiently

Strange, Fugue's code does not work for me, it throws an  error (that I mentioned in my earlier post).

Frequent Contributor
Posts: 129

Re: Sum array values more efficiently

Posted in reply to Anotherdream

Did not work for me until I followed Hai's suggestion and added OPTIONS VALIDVARNAME=ANY.  Have you tried that?

Super Contributor
Posts: 307

Re: Sum array values more efficiently

Posted in reply to Anotherdream

As Larry points out, you need to set OPTIONS VALIDVARNAME=ANY first. I assumed that the option would be specified (per my earlier post, and Hai's post).

Super User
Super User
Posts: 7,039

Re: Sum array values more efficiently

You mean the COUNTC() function.

Super Contributor
Posts: 307

Re: Sum array values more efficiently

Posted in reply to Anotherdream

If you columns are all character and you have no extraneous columns then this approach might work, too:

data sumacross ;

set test;

sum = count ( cats (of _character_), "Y");

run;

Super Contributor
Posts: 418

Re: Sum array values more efficiently

Posted in reply to Anotherdream

I do have options validvarname="ANY".

It's strange because I hvae two versions of SAS, one running on XP and another on Windows 7, and I just checked the code against the XP machine and it works, but it does not work on Windows 7.

Does anyone else have this problem?

Thanks!

Frequent Contributor
Posts: 129

Re: Sum array values more efficiently

Posted in reply to Anotherdream

I am running on Windows 7 with SAS 9.2, and this works.

Can you provide log from using Fugue's example?

Ask a Question
Discussion stats
  • 11 replies
  • 338 views
  • 0 likes
  • 5 in conversation