topic Re: how to count variable? in SAS Programming

how to count variable?

vicky07 — Fri, 07 Jun 2013 14:04:55 GMT

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!

Re: how to count variable?

Haikuo — Fri, 07 Jun 2013 14:22:13 GMT

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

Re: how to count variable?

vicky07 — Fri, 07 Jun 2013 14:57:51 GMT

Thank you!!

Re: how to count variable?

Naveen — Fri, 07 Jun 2013 15:45:18 GMT

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;