DATA Step, Macro, Functions and more

How to combine Loop and Group by?

Accepted Solution Solved
Reply
Occasional Contributor DiG
Occasional Contributor
Posts: 17
Accepted Solution

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:

ABCDEFyearsizecategory
372010M1
672005M1
22005L1
522007S2
842006M2
2010M3
32000S4
92005S4

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;

View solution in original post


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 DiG
Occasional Contributor
Posts: 17

How to combine Loop and Group by?

Thank you Larry, it worked!!

PROC Star
Posts: 7,366

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 DiG
Occasional Contributor
Posts: 17

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!!

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

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=_Smiley Happy 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 DiG
Occasional Contributor
Posts: 17

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=_Smiley Happy 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 DiG
Occasional Contributor
Posts: 17

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: 9,691

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 DiG
Occasional Contributor
Posts: 17

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: 7,366

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 DiG
Occasional Contributor
Posts: 17

Re: How to combine Loop and Group by?

Thank you!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1506 views
  • 6 likes
  • 5 in conversation