## How to combine Loop and Group by?

Solved
Occasional Contributor
Posts: 19

# How to combine Loop and Group by?

Good day,

I have a piece of code that runs through 6 fields in the table below and counts the number of non-zero values in each. Now my client wants this information by category (the number of non-zero entries in each field for each category). I added "by category" after the "set" line but it didn't work and still returned the total for each field. What is the right syntax to perform this task?

My table:

 A B C D E F year size category 3 7 2010 M 1 6 7 2005 M 1 2 2005 L 1 5 2 2007 S 2 8 4 2006 M 2 2010 M 3 3 2000 S 4 9 2005 S 4

My code:

data non_zero_entries (drop=i A--F);
set i end=e;
array a{*} A--F;
array a0{*} x1-x6;
do i=1 to DIM(a);
if a>0 or a<0 then a0+1;
end;
if e then output;
run;

Accepted Solutions
Solution
‎01-19-2012 10:42 AM
Frequent Contributor
Posts: 129

## Re: How to combine Loop and Group by?

Easiest thing to do given your code might be to add by group processing to reset your counter and to output multiple records.  Something like the following (assumes dataset sorted by category)

data non_zero_entries (drop=i A--F);
set i end=e;

by category ;
array a{*} A--F;
array a0{*} x1-x6;
do i=1 to DIM(a);

if first.category then a0 = 0 ;
if a>0 or a<0 then a0+1;
end;
if e if last.category then output;
run;

All Replies
Solution
‎01-19-2012 10:42 AM
Frequent Contributor
Posts: 129

## Re: How to combine Loop and Group by?

Easiest thing to do given your code might be to add by group processing to reset your counter and to output multiple records.  Something like the following (assumes dataset sorted by category)

data non_zero_entries (drop=i A--F);
set i end=e;

by category ;
array a{*} A--F;
array a0{*} x1-x6;
do i=1 to DIM(a);

if first.category then a0 = 0 ;
if a>0 or a<0 then a0+1;
end;
if e if last.category then output;
run;

Occasional Contributor
Posts: 19

## How to combine Loop and Group by?

Thank you Larry, it worked!!

PROC Star
Posts: 8,164

## How to combine Loop and Group by?

Your code is confusing because you try to reuse variable names as your array names, define an array with non-existing variables and, with your logic, count missing values as being non-zero.  Does the following come closer to what you are trying to do?

data i;

input A B C D E F year size \$ category;

cards;

3 . 7 . . . 2010 M 1

6 7 . . . . 2005 M 1

2 . . . . . 2005 L 1

5 2 . . . .          2007 S 2

. 8 . . . 4 2006 M 2

. . . . . . 2010 M 3

3 . . . . . 2000 S 4

. 9 . . . . 2005 S 4

;

data non_zero_entries (drop=i A--F year size);

set i;

by category;

array aa{*} A--F;

array x{6};

do i=1 to DIM(aa);

if first.category then x(i)=0;

x+not aa in (.,0);

end;

if last.category then output;

run;

Occasional Contributor
Posts: 19

## How to combine Loop and Group by?

art297, I tried your code after I had run the one suggested by LarryWorley. It works too! Thank you very much!!

Valued Guide
Posts: 765

## Re: How to combine Loop and Group by?

hi ... some other ideas ... two steps ...

data temp/view=temp;

set i;

array x(6) a--f;

do _n_ = 1 to 6;  x(_n_) = (x(_n_) ne .); end;

run;

proc summary data=temp nway;

class category;

var a--f;

output out=want (drop=_ sum=;

run;

or one PROC ...

proc sql;

create table want as

select category,

sum((a ne .)) as a, sum((b ne .)) as b, sum((c ne .)) as c,

sum((d ne .)) as d, sum((e ne .)) as e, sum((f ne .)) as f

from i

group category;

quit;

Occasional Contributor
Posts: 19

## Re: How to combine Loop and Group by?

Thank you Mike for your input. I tried the two-step code. Proc summary didn't return any totals, just one variable 'Category'.

Valued Guide
Posts: 765

## Re: How to combine Loop and Group by?

hi ... it does work with Art's data ...

data i;

input A B C D E F year size \$ category;

cards;

3 . 7 . . . 2010 M 1

6 7 . . . . 2005 M 1

2 . . . . . 2005 L 1

5 2 . . . . 2007 S 2

. 8 . . . 4 2006 M 2

. . . . . . 2010 M 3

3 . . . . . 2000 S 4

. 9 . . . . 2005 S 4

;

data temp/view=temp;

set i;

array x(6) a--f;

do _n_ = 1 to 6;  x(_n_) = (x(_n_) ne .); end;

run;

proc summary data=temp nway;

class category;

var a--f;

output out=want (drop=_ sum=;

run;

proc print data=want;

run;

Obs    category   A    B    C    D    E    F

1         1       3    1    1    0    0    0

2         2       1    2    0    0    0    1

3         3       0    0    0    0    0    0

4         4       1    1    0    0    0    0

and following up on the Ksharp SQL suggestion and Art's note ... one additional  thing you have to do in this example is limit the variables that are placed in the macro variable ... that's easy to do with VARNUM (and you would use the same approach even if you have many more variables, it's easy to specify ranges of variable numbers using the IN operatpr)...

proc sql noprint;

select catx(' ', 'count(', name ,') as ',name) into :counts separated by ','

from dictionary.columns

where libname eq 'WORK' and memname eq 'I' and varnum in (1:6);

create table want as

select category, &counts

from i

group category;

quit;

Occasional Contributor
Posts: 19

## Re: How to combine Loop and Group by?

Mike, the first code still doesn't work but thank you for mentioning VARNUM. Could be useful in the future.

Super User
Posts: 10,770

## Re: How to combine Loop and Group by?

```data i;
input A B C D E F year size \$ category;
cards;
3 . 7 . . . 2010 M 1
6 7 . . . . 2005 M 1
2 . . . . . 2005 L 1
5 2 . . . .          2007 S 2
. 8 . . . 4 2006 M 2
. . . . . . 2010 M 3
3 . . . . . 2000 S 4
. 9 . . . . 2005 S 4
;
run;

proc sql;
create table want as
select category,count(a) as a,count(b) as b,count(c) as c,count(d) as d,count(e) as e,count(f) as f
from i
group by category;
quit;

```

Ksharp

Occasional Contributor
Posts: 19

## Re: How to combine Loop and Group by?

Hi Ksharp,

Thanks for your response - your code seems to be the simpliest one. I would like to use it in the future should I face the same task again but I never used SQL in SAS before. The example of table I provided here is very basic. I intentionally simplified it just to get an idea. In fact, my real table contains over 100 fields, therefore, I cannot list them individually in the SQL statement. Is there a way around it?

PROC Star
Posts: 8,164

## Re: How to combine Loop and Group by?

Yes.  You can use proc sql, with dictionary.columns, to build a macro variable that contains a comma separated list of all your variables including the count statement.

There are numerous examples in other forum posts.  The keywords to look for would be sql, dictionary.columns and into.  I don't know what your file structure looks like but it could be as simple as preceding Ksharp's suggested code with something like:

proc sql noprint;

select 'count('||name||') as '||name

into :vars separated by ','

from dictionary.columns

where libname="WORK" and

memname="HAVE"

;

You could then replace Ksharp's suggested select clause with something as simple as:

select &vars.

Occasional Contributor
Posts: 19

## Re: How to combine Loop and Group by?

Thank you!

🔒 This topic is solved and locked.