topic Re: count of the number of one in column in SAS Programming

count of the number of one in column

soham_sas — Thu, 24 May 2018 14:08:40 GMT

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

Re: count of the number of one in column

ballardw — Thu, 24 May 2018 14:13:49 GMT

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;

Re: count of the number of one in column

soham_sas — Thu, 24 May 2018 14:22:28 GMT

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

Re: count of the number of one in column

PaigeMiller — Thu, 24 May 2018 14:37:38 GMT

@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;

Re: count of the number of one in column

novinosrin — Thu, 24 May 2018 14:31:44 GMT

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;

Re: count of the number of one in column

novinosrin — Thu, 24 May 2018 14:37:05 GMT

@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.

Re: count of the number of one in column

Amir — Thu, 24 May 2018 15:17:20 GMT

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.

Re: count of the number of one in column

PaigeMiller — Thu, 24 May 2018 15:20:11 GMT

@Amir

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

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

Re: count of the number of one in column

Amir — Thu, 24 May 2018 15:48:48 GMT

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.

Re: count of the number of one in column

PaigeMiller — Thu, 24 May 2018 15:51:33 GMT

Okay, this is the equivalent of

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

in my code.

Re: count of the number of one in column

ballardw — Thu, 24 May 2018 16:16:28 GMT

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;

Re: count of the number of one in column

jim_cai — Fri, 25 May 2018 14:29:49 GMT

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;

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;