Counting fields 1

[DonaZ]

Hi,

I need help. I want to count all the fields on a record that have a value.
I do not want to count the fields with a null value. I want to add up all the NonNull fields on a single record in one new column.

Any ideas?


Thank you for your time

 

[ChrisW75]

the N() function will count up non-missing numeric fields.

  count = n(var1,var2...varn);
* or... *;
  count = n(of var1-varn);

To sum up the non missing values use the sum() function.

  sum = sum(var1,var2,var3...varn);
* or... *;
  sum = sum(of var1-varn);

The "of" is important, without it, the function evaluates Var1-varn, then counts or sums the result (ie 1 for the count).

data _null_;
  var1=2;
  var2 = 0;
  var3 = .;
  var4 = 5;

  count1 = n(var1-var4);
  put count1=;

  count2 = n(of var1-var4);
  put count2=;
run;

Note - These only work on NUMERIC data.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[alunbrain]

Hi
I suggest eithe

data temp;
set ;
if var1 ^=. then count=1;
else count=0;
/* if character */

if var1 ^= ' ' then count=1;
else count=0;
all='all';
run;

/* or could use*/
data temp;
set ;
count = (var1 ^=.); /* ^= or ne whatever you prefer */
/* if character */

count = (var1 ^= ' ');
all='all';
run;

proc summary data = temp nway;
class all;
var count;
output out = temp1 sum=;
run;

cheers

 

[klaz2002]

While Chris's method will work, you need to know the names of the fields in advance. alunbrain's method I don't follow, any variable after the first one would not be counted/summed.

Here is how I would tackle this problem.

data test;
  set your_data;
  *** INIT YOUR COUNTER/SUM VARS ***;
  count = 0;
  total = 0;
  *** ADD ONLY NUMERIC VARS TO YOUR ARRAY ***;
  array t (*) _numeric_;

  do i=1 to dim(t); 
   if t(i) > . and vname(t(i)) not in('count','total', 'i') then do;
     count + 1;
     total + t(i); 
   end;
  end;
run;

You will now have two extra vars that hold both a count of the non-nulls and their sum (total).

Hope that you can use this....
Klaz

 

[ChrisW75]

Alunbrain - This is a method for counting up how many records have a value in a variable, whereas the question suggests that DonaZ is looking to count the number of populated variables in a single record.
Your method is about as good as you can get for character variables, however, for numerics, you can actually reduce it further, using only the proc summary step.

proc summary data =dset1 nway;
  class classvar;
  var var1;
  output out =temp1 n=;
run;

Also, if you want to sum the dataset down to one variable, rather than adding an "all" variable to use as your class variable, you can actually eliminate the class variable altogether:-

proc summary data =dset1 nway;
  var var1;
  output out=temp1 n=;
run;

Good of you to post though, keep it up. I hope this helps you on your way to SAS Guru-dom.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[DonaZ]

I forgot to ask if there is a proc SQL version for this query.
What would be faster? Once I total the fields, I want the overall of all the records.
Example:
Total number of Attendance, Total Number of fields with a positive variable (this number would be from adding up the number of fields with variables)

Thank you for your time. I am in no hurry.

 

[ChrisW75]

I don't think so, not easily anyway. The sum function in proc SQL works down rather than across (ie it'll sum a column across observations, rather than sum a group of columns together).
I tried this code below, but it returns useless results (note, using + to add fields together returns a missing value if one of the values is missing)

data dset1;
  var1 = 1;
  var2 = .;
  var3 = 5;
  output;

  var1 = .;
  var2 = 2;
  var3 = 4;
  output;

  var1 = 1;
  var2 = 2;
  var3 = 3;
  output;
run;

proc sql;
  create table blah as
  select sum(var1+var2+var3)  as sum
        ,n(var1)  as n_var1
        ,n(var2)  as n_var2
        ,n(var3)  as n_var3
  from dset1
  ;
quit;

PROC SQL isn't the way to go with this one.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[DonaZ]

Thank you everyone for all your responses!

Have a good weekend.

 

[alunbrain]

with sql
use the group by function


proc sql;
create table blah as
select 'all' as all,
sum(var1+var2+var3) as sum
from dset1
group by all;
quit;

cheers

 

[kdt82]

proc sql seems to work fine here.

If you pass only one argument to a proc sql summary function, it will summarise the column, if you specify multiple variables, it will summarise the variables.

data test;
input x y z;
cards;
1 . 3
2 5 6
. 6 3
;
run;

proc sql noprint;
create table test2 as
select sum(x,y,z) as row_sum, sum(calculated row_sum) as total
from test;
quit;

test2
row_sum total
4 26
13 26
9 26