topic Re: How to count the number of observations has variable value greater than the current value? in SAS Programming

How to count the number of observations has variable value greater than the current value?

cxterm — Fri, 16 Mar 2018 20:12:09 GMT

Hi all,

Here is a sample dataset with variable A

Now I want a cnt variable CNT like

A CNT

1 2 (since there a 2 '2' just before the next '1')

2 0 (since there no '3' just before the next '2')

2 0

1 0

1 3 (there are 3 '2' before the next '1')

2 0

2 2 (there are 2 '3' before the next '2')

3 0

2 0

1 0

How could I got the CNT? Any help?

Thanks in advance!

Re: How to count the number of observations has variable value greater than the current value?

PGStats — Sat, 17 Mar 2018 04:20:41 GMT

You can do this counting with random access:

data want;
set have nobs=nobs;
cnt = 0;
do point = _n_+1 to nobs until(_a = a);
    set have (rename=(a=_a)) point=point;
    cnt = cnt + (_a = a + 1);
    end;
drop _a;
run;

Re: How to count the number of observations has variable value greater than the current value?

Ksharp — Sat, 17 Mar 2018 14:23:45 GMT

Hash Table is my last resort .

data have;
input A;
cards;
1
2
2
1
1
2
2
3
3
2
1
;
data have;
 set have ;
 by a notsorted;
 group+first.a;
run;
data want;
 if _n_=1 then do;
  if 0 then set have(rename=(a=_a));
  declare hash h(dataset:'have(rename=(a=_a))',multidata:'y');
  h.definekey('group');
  h.definedata('_a');
  h.definedone();
 end;
set have;
by group;
count=0;
if last.group then do;
  _group=group+1;
  rc=h.find(key:_group);
  do while(rc=0);
    if _a=a+1 then count+1;
    rc=h.find_next(key:_group);
  end;
end;
drop _: rc group;
run;
proc print;run;

Re: How to count the number of observations has variable value greater than the current value?

cxterm — Sat, 17 Mar 2018 15:01:40 GMT

Thank you! works great!

Re: How to count the number of observations has variable value greater than the current value?

cxterm — Sat, 17 Mar 2018 15:03:43 GMT

It also works although I select the way a little codings. Thank you!