topic Re: Counter Variable without sorting in SAS Procedures

Counter Variable without sorting

CharlesFowler72 — Wed, 18 Jul 2018 14:09:07 GMT

I need to create a counter variable which does not have to be sorted. It cannot be sorted as the order is custom on a number of different other criteria, essentially it looks random but isn't in the larger dataset. The output counter would look like below using VAR_1 as the key variable.

VAR_1	Counter
A	1
C	1
A	2
B	1
A	3
C	2

Re: Counter Variable without sorting

Ksharp — Wed, 18 Jul 2018 14:19:06 GMT

data have;
input VAR_1 $ /;
cards;
A
1
C
1
A
2
B
1
A
3
C
2
;
run;
data want;
if _n_=1 then do;
 if 0 then set have;
 declare hash h();
 h.definekey('var_1');
 h.definedata('count');
 h.definedone();
end;
 set have;
 if h.find()=0 then do;count=count+1;h.replace();end;
  else do;count=1;h.add();end;
run;
 
proc print;run;

Re: Counter Variable without sorting

Astounding — Wed, 18 Jul 2018 14:25:03 GMT

@Ksharp glad you posted this. I was thinking, "If only I knew hashing." This is the final piece of evidence that I must learn it.

Re: Counter Variable without sorting

Ksharp — Wed, 18 Jul 2018 14:27:37 GMT

Sure. Hash Table is a programming tool you must learn.

Re: Counter Variable without sorting

FreelanceReinh — Wed, 18 Jul 2018 17:30:08 GMT

@Astounding wrote:

This is the final piece of evidence that I must learn it.

I can recommend it and I've just noticed that the book "Data Management Solutions Using SAS® Hash Table Operations: A Business Intelligence Case Study" by Paul Dorfman and Don Henderson has now been published. It was mentioned in this interesting paper by the authors, which they called "a preview of [that] SAS book".

When I started learning hash object programming there was only Michele M. Burlew's book "SAS® Hash Object Programming Made Easy". In October 2016 I wrote a customer review (titled "Valuable resource for intermediate and advanced SAS programmers") about it on a very well known online bookseller's website. Since then, the hash object has become one of my must-have tools.

Re: Counter Variable without sorting

novinosrin — Thu, 19 Jul 2018 14:50:23 GMT

data have;
input VAR_1 $ /;
cards;
A
1
C
1
A
2
B
1
A
3
C
2
;
run;

data want;
set have;
array t(3,2) _temporary_;
_k=rank(VAR_1);
_k2=whichn(_k, of t(*));
if _k2=0 then do;_n+1; t(_n,1)=_k;count=1;t(_n,2)=count;end;
else do;count=t(_k2,2)+1;t(_k2,2)=count;end;
drop _:;
run;