topic Re: Help - count horizontally with a numeric condition in New SAS User

Help - count horizontally with a numeric condition

kritiment — Sat, 04 Mar 2023 07:51:59 GMT

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.

Re: Help - count horizontally with a numeric condition

Kurt_Bremser — Sat, 04 Mar 2023 08:20:32 GMT

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.

Re: Help - count horizontally with a numeric condition

ballardw — Sat, 04 Mar 2023 14:37:30 GMT

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_: );

Re: Help - count horizontally with a numeric condition

Patrick — Sat, 04 Mar 2023 23:27:25 GMT

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;

Re: Help - count horizontally with a numeric condition

kritiment — Wed, 08 Mar 2023 11:35:02 GMT

@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,..