DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

Hi all,

Here is a sample dataset with variable A

 

A

1

2

2

1

1

2

2

3

3

2

1

 

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

3    0

2    0

1    0

 

How could I got the CNT? Any help? 

Thanks in advance!


Accepted Solutions
Solution
‎03-17-2018 11:01 AM
Esteemed Advisor
Posts: 5,625

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

[ Edited ]

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;
PG

View solution in original post


All Replies
Solution
‎03-17-2018 11:01 AM
Esteemed Advisor
Posts: 5,625

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

[ Edited ]

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;
PG
New Contributor
Posts: 3

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

Thank you! works great!

Super User
Posts: 10,850

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

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;
New Contributor
Posts: 3

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

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

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 187 views
  • 1 like
  • 3 in conversation