DATA Step, Macro, Functions and more

how to count variable?

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

how to count variable?

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!


Accepted Solutions
Solution
‎06-07-2013 10:22 AM
Respected Advisor
Posts: 3,124

Re: how to count variable?

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

View solution in original post


All Replies
Solution
‎06-07-2013 10:22 AM
Respected Advisor
Posts: 3,124

Re: how to count variable?

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

Contributor
Posts: 71

Re: how to count variable?

Thank you!!

N/A
Posts: 1

Re: how to count variable?

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 219 views
  • 1 like
  • 3 in conversation