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:
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;
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;
Thank you Larry, it worked!!
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;
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"
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;
Thank you Mike for your input. I tried the two-step code. Proc summary didn't return any totals, just one variable 'Category'.
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;
Mike, the first code still doesn't work but thank you for mentioning VARNUM. Could be useful in the future.
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
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?
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.
Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.