BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
LarryWorley
Fluorite | Level 6

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

12 REPLIES 12
LarryWorley
Fluorite | Level 6

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;

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

Thank you Larry, it worked!!

art297
Opal | Level 21

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;

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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

MikeZdeb
Rhodochrosite | Level 12

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;

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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

MikeZdeb
Rhodochrosite | Level 12

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;

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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

Ksharp
Super User
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

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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?

art297
Opal | Level 21

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.

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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