DATA Step, Macro, Functions and more

count of the number of one in column

Reply
Contributor
Posts: 40

count of the number of one in column

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_namecount
Var13
Var23
Var35
Var45
Var54
Var67
Var74
Super User
Posts: 13,304

Re: count of the number of one in column

Posted in reply to soham_sas

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;

Contributor
Posts: 40

Re: count of the number of one in column

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 

Respected Advisor
Posts: 2,812

Re: count of the number of one in column

[ Edited ]
Posted in reply to soham_sas

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

 

--
Paige Miller
PROC Star
Posts: 1,570

Re: count of the number of one in column

Posted in reply to soham_sas
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;
PROC Star
Posts: 1,570

Re: count of the number of one in column

Posted in reply to 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.

Super Contributor
Posts: 322

Re: count of the number of one in column

Posted in reply to soham_sas

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.

Respected Advisor
Posts: 2,812

Re: count of the number of one in column

[ Edited ]

@Amir

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

 

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

 

--
Paige Miller
Super Contributor
Posts: 322

Re: count of the number of one in column

Posted in reply to PaigeMiller

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.

Respected Advisor
Posts: 2,812

Re: count of the number of one in column

Okay, this is the equivalent of

 

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

in my code.

--
Paige Miller
Super User
Posts: 13,304

Re: count of the number of one in column

[ Edited ]
Posted in reply to soham_sas

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;
Occasional Contributor
Posts: 7

Re: count of the number of one in column

Posted in reply to soham_sas

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;

 

Ask a Question
Discussion stats
  • 11 replies
  • 160 views
  • 0 likes
  • 6 in conversation