count of the number of one in column

[soham_sas]

Hi 

i have a data set like below , i want to count the number of One (1) present in each variable 

Data have;
infile cards dlm= '09'x;
input Var1-var7;
cards;
0 0 0 0 1 1 1
0 0 0 0 0 1 1
1 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 1 0 1 0
0 0 0 0 0 0 1
0 0 1 1 1 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 1 0 1 0
0 0 1 1 1 0 0
1 1 0 0 0 0 0
0 0 0 0 1 1 1
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 0 0 1 0
0 0 0 0 0 0 0
1 0 0 0 0 1 0
0 0 1 1 0 0 0

;
run;

Final output should be like below 

Var_name	count
Var1	3
Var2	3
Var3	5
Var4	5
Var5	4
Var6	7
Var7	4

[ballardw]

If the variable values are all ones and zeroes then the easiest might be to sum them:

You do not say whether you need a data set or a report.

Proc means data=have sum maxdec=0;

   var var1-var7;

run;

[soham_sas]

Hi , i want to create a data set out of it to be used in next step 

and the values are other than 0 and 1 also in the real time data 

[PaigeMiller]

---

@soham_sas wrote:

and the values are other than 0 and 1 also in the real time data 

---

This is an important detail that should have been mentioned in the original post, as we can only go by the example you give us, and we can't write code for other data that is different than the example you give.

In this case, where the values can be other than 0 or 1, you can use PROC FREQ, or do this counting of values that equal 1 in a data step.

UNTESTED CODE

data want;
    set have end=eof;
    array v var1-var7;
    array c count1-count7;
    do i=1 to 7; 
         if v(i)=1 then c(i)+1;
    end;
    if eof then output;
    keep var1-var7;
run;

[novinosrin]

Data have;
infile cards truncover ;
input Var1-var7;
cards;
0 0 0 0 1 1 1
0 0 0 0 0 1 1
1 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 1 0 1 0
0 0 0 0 0 0 1
0 0 1 1 1 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 1 0 1 0
0 0 1 1 1 0 0
1 1 0 0 0 0 0
0 0 0 0 1 1 1
0 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 0 0 0 1 0
0 0 0 0 0 0 0
1 0 0 0 0 1 0
0 0 1 1 0 0 0

;
run;

Proc means data=have  maxdec=0;

   var var1-var7;
output out=temp(drop=_:) sum=/autoname;
run;

proc transpose data=temp out=want;
run;

[novinosrin]

@soham_sas wrote:

and the values are other than 0 and 1 also in the real time data 

Oh well, filter your dataset with a where condition and make the proc means solution work. Yes, it will take one more step but very easy to follow.

[Amir]

Hi,

Borrowing the array definitions from @PaigeMiller, you could try the following:

data want(keep = var_name count);
   array v var1-var7;
   array c count1-count7;

   do until(last_obs);
      set have end = last_obs;
      
      do i=1 to 7;
         c[i] + v[i] = 1;
      end;
   end;

   do i=1 to 7;
      var_name = cats('Var',i);
      count    = c[i];
      output;
   end;
run;

Regards,

Amir.

[PaigeMiller]

@Amir

I'm afraid I don't understand this step or see how it helps:

c[i] + v[i] = 1;

[Amir]

Hi @PaigeMiller,

Perhaps a comment or some brackets would have aided clarity, e.g.:

c[i] + (v[i] = 1);

I.e., perform the Boolean test v[i] = 1 which should return a 1 or a 0, then add that Boolean result to the retained value of c[i].

The output appears to match what the OP specified.

I hope that makes more sense.

Regards,

Amir.

[PaigeMiller]

Okay, this is the equivalent of

if v(i)=1 then c(i)+1;

in my code.

[ballardw]

If you do not have millions of values this might be an approach:

Data have;
infile cards truncover ;
input Var1-var7;
cards;
0 0 0 0 1 1 1
0 0 0 0 0 1 1
1 0 0 0 0 0 0
0 0 1 0 0 0 0
0 1 2 1 0 1 0
0 0 0 0 0 0 1
0 0 1 1 1 0 0
0 3 0 0 0 0 0
8 0 0 0 0 0 0
0 0 0 0 0 0 0
0 0 0 0 6 0 0
0 0 0 0 0 0 7
0 0 0 1 0 1 0
0 0 1 1 1 0 0
;
run;
proc transpose data=have out=work.trans;
var var1-var7;
run;

data want;
  set work.trans;
  count1 =count(catx(',',of col:),'1');
  drop col: ;
run;

if you have more than 100 single digits you may need to estimate the length of the combined delimited string of all the rows of data and build a specific string variable long enough to hold that:

data want;
  set work.trans;
  length str $ 1000;
  str = catx(',',of col:);
  count1 =count(str,'1');
  drop col: str;
run;

[jim_cai]

with data set HAVE created, the procedure of frequency summary could be one of the following:

ods output onewayfreqs=freq;

proc freq data=have;
   table _all_/nopercent nocum list sparse;
quit;

ods output close;

data final;
   set;
   drop table frequency var1-var7 var_level f_:;
   var_level = strip(coalescec(of f_:));
   if var_level='1';
   var_name = scan(table, -1, '');
   count = frequency;
run;

or

data _null_;
   set have( obs=1);
   array var[7];
   n = dim(var);
   do i=1 to n;
      v_name = vname(var[i]);
      if i=1 then do;
         call execute('proc sql;');
         call execute('   create table final as');
      end; else call execute('   union');
      call execute('   select "'||trim(v_name)||'" as var_name, sum('||trim(v_name)||'=1) as count from have');
      if i=n then do;
         call execute('   ;');
         call execute('quit;');
      end;
   end;
run;