how to count variable?

[vicky07]

Hi,

I'm looking to count value of columns if it s not '00000' by each id. Below is my code and desired output. Basically, i dont want to count any column if the value is 00000. Any help is much appreciated!

Data a;
Input grp $3. Mon : $3.  col1 : $5. col2 : $5. col3 : $5. col4 : $5.;
Infile cards;
Cards;
AGH JAN 45433 99348 23489 00000
NIK  AUG 45667 81290 33667 77552
DET DEC 63644 56712 00000 00000
AAA MAR 78996 00000 00000 00000
UCG DEC 00000 00000 00000 00000
;
Run;

Output

ID MonTotal
AGH JAN  3
NIK  AUG  4
DET DEC  2
AAA MAR 1
UCG DEC 0

Thanks!

[Haikuo · Accepted solution]

Data a;

Input grp :$8. Mon : $8. col1 : $5. col2 : $5. col3 : $5. col4 : $5.;

Infile cards;

Cards;

AGH JAN 45433 99348 23489 00000

NIK AUG 45667 81290 33667 77552

DET DEC 63644 56712 00000 00000

AAA MAR 78996 00000 00000 00000

UCG DEC 00000 00000 00000 00000

;

Run;

/* data step*/

data want;

set a;

  array col col:;

  total=0;

  do over col;

  if col ne '00000' then total+1;

  end;

  drop col:;

run;

/*Proc SQL*/

proc sql;

  create table want_sql as

  select grp,mon,sum(col1 ne '00000',

col2 ne '00000',

col3 ne '00000',

col4 ne '00000') as total

from a;

quit;

Haikuo

[vicky07]

Thank you!!

[Naveen]

Hi,

Try this:

data b ;

set a ;

array col(*) col1 - col4 ;

total = 0 ;

do i = 1 to dim(col) ;

     if col(i) ne "0000" then total = total + 1  ;

end;

keep Mon total ; 

run;