Help - count horizontally with a numeric condition

[kritiment]

I have a dataset with 1 million records and 24 variables (with the same prefix for example month_1, month_2, month_3 up to month_24), plus ID.

For each record i want to count the number of month_: where month_x is greater than 0.

How do I achieve this?

Any suggestion would be really appreciated.

[Kurt_Bremser]

proc transpose data=have out=long;
by id;
var month_:;
run;

proc sql;
create table want as
  select
    id,
    sum(col1 > 0) as count
  from long
  group by id
;
quit;

Maxim 19: Long Beats Wide.

[ballardw]

Do you actually have values of 0 or are they missing? Different beasts in SAS.

If the variables have missing instead of 0 or negative values in a data step;

countmonth = n(of month_: );

[Patrick]

Long beats wide certainly applies.

Below how you can dynamically build an expression where zero or missing becomes 0 and any other value 1 so that you just can sum the elements to get your count.

data have;
  array month_ {24} 8;
  month_7=10;
  month_9=5;
  month_24=-10;
  month_2=0;
run;

%let expr_list=;
proc sql;
  select catx(' ',name,'not in (0,.)') into :expr_list separated by ','
  from dictionary.columns
  where libname='WORK' and memname='HAVE' and upcase(name) like 'MONTH^_%' escape '^'
  ;
quit;
%put %nrbquote(&expr_list);

data want;
  length cnt_not0orMiss 8;
  set have;
  cnt_not0orMiss=sum(&expr_list);
run;

[kritiment]

---

@kritiment wrote:

I have a dataset with 1 million records and 24 variables (with the same prefix for example month_1, month_2, month_3 up to month_24), plus ID.

For each record i want to count the number of month_: where month_x is greater than 0.

How do I achieve this?

Any suggestion would be really appreciated.

---

I got this,..