topic How to combine Loop and Group by? in SAS Programming

How to combine Loop and Group by?

DiG — Thu, 19 Jan 2012 15:07:56 GMT

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;

Re: How to combine Loop and Group by?

LarryWorley — Thu, 19 Jan 2012 15:42:28 GMT

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;

How to combine Loop and Group by?

art297 — Thu, 19 Jan 2012 15:43:06 GMT

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;

How to combine Loop and Group by?

DiG — Thu, 19 Jan 2012 15:54:12 GMT

Thank you Larry, it worked!!

How to combine Loop and Group by?

DiG — Thu, 19 Jan 2012 16:07:35 GMT

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

The "Correct answer" tag for some reason dissapeared so I mark your response as "Helpful"

Re: How to combine Loop and Group by?

MikeZdeb — Fri, 20 Jan 2012 02:56:21 GMT

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;

Re: How to combine Loop and Group by?

Ksharp — Fri, 20 Jan 2012 07:06:13 GMT

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

Re: How to combine Loop and Group by?

DiG — Fri, 20 Jan 2012 15:51:04 GMT

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?

Re: How to combine Loop and Group by?

art297 — Fri, 20 Jan 2012 16:00:40 GMT

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.

Re: How to combine Loop and Group by?

DiG — Fri, 20 Jan 2012 16:06:49 GMT

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

Re: How to combine Loop and Group by?

DiG — Fri, 20 Jan 2012 16:10:30 GMT

Thank you!

Re: How to combine Loop and Group by?

MikeZdeb — Fri, 20 Jan 2012 16:47:38 GMT

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;

Re: How to combine Loop and Group by?

DiG — Mon, 23 Jan 2012 15:24:29 GMT

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