Running Count by Multiple Variables Where One Variable is NotSorted

[P5C768]

Hello,

I am trying to create a running count by multiple variables where one ID variable is sorted, but the second variable is notsorted (the data set must remain sorted by a timestamp, which was not included in this sample).  Notice that for id = 1, a simple first. count variable would work, but for ID 2, it fails.  The count variable is the one I would like to create.  Any assistance in creating the count variable to match the sample output below is much appreciated. 

data rank;
	set sess;
	by id notsorted ch notsorted;

	if first.id then Count = 0;
		Count + 1;
	if first.ch then Count = 1;
run;

id	ch	Count
1	P	1
1	P	2
1	P	3
1	O	1
2	W	1
2	I	1
2	W	2
2	I	2
2	P	1
2	P	2
2	O	1
3	O	1
3	P	1
3	P	2
4	W	1
4	I	1
4	W	2
4	I	2
4	W	3
4	I	3
4	P	1
4	P	2
4	O	1

[novinosrin · Accepted solution]

Hello @P5C768  Pretty frequently asked question I think 

data have;
infile cards expandtabs;
input id $	ch $;*	Count ;
cards;
1	P	1
1	P	2
1	P	3
1	O	1
2	W	1
2	I	1
2	W	2
2	I	2
2	P	1
2	P	2
2	O	1
3	O	1
3	P	1
3	P	2
4	W	1
4	I	1
4	W	2
4	I	2
4	W	3
4	I	3
4	P	1
4	P	2
4	O	1
;

data want ;
if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("id","ch") ;
   h.definedata ("count") ;
   h.definedone () ;
end;
do until(last.id);
 set have;
 by id ;
 count=ifn( h.find()=0 , sum(count,1),1);
 h.replace();
 output;
end;
h.clear();
run;

/*Or*/ 

data have;
infile cards expandtabs;
input id $	ch $;*	Count ;
cards;
1	P	1
1	P	2
1	P	3
1	O	1
2	W	1
2	I	1
2	W	2
2	I	2
2	P	1
2	P	2
2	O	1
3	O	1
3	P	1
3	P	2
4	W	1
4	I	1
4	W	2
4	I	2
4	W	3
4	I	3
4	P	1
4	P	2
4	O	1
;


data want;
 do until(last.id);
  set have;
  by id ;
  array t(99999) _temporary_ ;
  array j(99999)$ _temporary_;
  if ch in j then do;
  _k=whichc(ch,of j(*));
  count=sum(t(_k),1);
  t(_k)=count;
  end;
  else do;
  _n+1;
  j(_n)=ch;
  count=1;
  t(_n)=count;
  end;
  output;
 end;
call missing(of j(*),of t(*),_n);
drop _:;
run;

[P5C768]

Thank you so much @novinosrin !  Worked like a charm!

[Tom]

@novinosrin 

No need to include any data in the hash.  Also if you going to include ID in the hash keys there is no need to clear it.

data want ;
  set have;
  by id ;
  if _n_=1 then do;
    dcl hash h() ;
    h.definekey('id','ch') ;
    h.definedone() ;
  end;
  if first.id then count=0;
  count+(0=h.add());
run;

[novinosrin]

@Tom  Thank you Sir for the note. I'm afraid if we do not include the data portion, the hash object compile/execution time operaton anyway does an automatic inclusion of keys as data too consequently doubling the hash entry length and memory footprint;

For example; To test what's in hash table , I added the output method to  your code to view the contents

data want ;
  set have end=z;
  by id ;
  if _n_=1 then do;
    dcl hash h() ;
    h.definekey('id','ch') ;
    h.definedone() ;
  end;
  if first.id then count=0;
  count+(0=h.add());
if z then h.output(dataset:'whats_in_hash_table');
run;

You would notice Id and ch combo are populated as data values in hash too confirming my initial point.-whats_in_hash_table

Secondly, your boolean approach to increment count is great had ch been sorted i'd think, but i'm afraid we would need the count previously enumerated parked somewhere to retrieve and replace be it in a hash table or an array. Methinks this where OP prolly was perhaps stuck. 

Thirdly, Clear() helps in freeing up memory after processing each by group. A way to manage memory. The combo idea of ID and CH as opposed to just CH helps testing and debugging effectively since visualizing the contents of hash tables makes it comfy to know what's going on.

PS  The best fun tricky part of hashes that cause confusion is, when the keys and data portion are the same , the data portion alone can be modified. 🙂

[Tom]

---

@novinosrin wrote:

@Tom  Thank you Sir for the note. I'm afraid if we do not include the data portion, the hash object compile/execution time operaton anyway does an automatic inclusion of keys as data too consequently doubling the hash entry length and memory footprint;

For example; To test what's in hash table , I added the output method to  your code to view the contents

data want ;
  set have end=z;
  by id ;
  if _n_=1 then do;
    dcl hash h() ;
    h.definekey('id','ch') ;
    h.definedone() ;
  end;
  if first.id then count=0;
  count+(0=h.add());
if z then h.output(dataset:'whats_in_hash_table');
run;

You would notice Id and ch combo are populated as data values in hash too confirming my initial point.-whats_in_hash_table

I don't really know how hash tables are stored in memory, but they obviously need to know the keys in order for them to work. So adding data variables also will by definition take more space.  Your test of what gets written to an output dataset probably has little to do with what is actually stored in memory.

Secondly, your boolean approach to increment count is great had ch been sorted i'd think, but i'm afraid we would need the count previously enumerated parked somewhere to retrieve and replace be it in a hash table or an array. Methinks this where OP prolly was perhaps stuck. 

Not sure what you point is.  The normal variable COUNT has the current count.  Whether more logic is needed or not depends on what the original question actually wants.

Thirdly, Clear() helps in freeing up memory after processing each by group. A way to manage memory. The combo idea of ID and CH as opposed to just CH helps testing and debugging effectively since visualizing the contents of hash tables makes it comfy to know what's going on.

PS  The best fun tricky part of hashes that cause confusion is, when the keys and data portion are the same , the data portion alone can be modified. 🙂

---

If you are going to clear the hash after each ID group then there is no need to store the ID in the hash.

[Tom]

What is the point of the running count if the data is not sorted by that second variable?

It is not at all clear what you are counting. In the first value of ID you seem to be counting the row number within the lowest level by group.  But in the second value of ID you seem to be counting the number of distinct CH values within that ID, but then it violates that after awhile.

[PaigeMiller]

Sort the data by IDs, do the counts, then "un-sort" by timestamp