topic Re: Count the number of times a ky appeared individually in SAS Programming

Count the number of times a key appeared individually

iSAS — Tue, 28 Jan 2020 12:07:25 GMT

Let's say I have a data below:

data have;
infile cards truncover;
input ID :$4.;
cards;
0196
0196
0196
0395
0752
1277
1277
;
run;

What I want is to have a new table with a new column wherein it will count the number of times the ID appeared but not group it. To explain it clearer, I want to have an output like this:

ID        Count_ID
0196          1
0196          2
0196          3
0395          1
0752          1
1277          1
1277          2

Could someone help me on this? A program that will work even if ID is not sort by order

Re: Count the number of times a ky appeared individually

Sathish_jammy — Tue, 28 Jan 2020 11:59:09 GMT

Data want;
  Set Have;
  Count_ID + 1;
  By ID;
  if first.ID then Count_ID = 1;
Run;

Re: Count the number of times a ky appeared individually

PeterClemmensen — Tue, 28 Jan 2020 12:01:26 GMT

As requested, this works regardless of the sort order

data want;
    if _N_=1 then do;
        declare hash h();
        h.definekey('id');
        h.definedata('Count_ID');
        h.definedone();
    end;

    set have;

    if h.find() ne 0 then Count_ID = 1;
    else                  Count_ID + 1;

    h.replace();
run;

Result:

ID    Count_ID 
0196  1 
0196  2 
0196  3 
0395  1 
0752  1 
1277  1 
1277  2

Re: Count the number of times a key appeared individually

novinosrin — Tue, 28 Jan 2020 13:12:07 GMT

Hi @iSAS For unsorted count, Hash is easy and convenient. While the sorted and Hash solutions have already been posted, I was thinking what if your ID are all digits and do have any alpha char. If this is true, key indexing is very viable and yet another convenient solution



data have;
infile cards truncover;
input ID :$4.;
cards;
0196
0196
0196
0395
0752
1277
1277
;
run;

data want;
 do until(z);
  set have end=z;
  array t(999999)_temporary_;
  _n_=input(id,32.);
  if t(_n_) then t(_n_)=sum(t(_n_),1);
  else t(_n_)=1;
  count_id=t(_n_);
  output;
 end;
run;

Re: Count the number of times a key appeared individually

iSAS — Tue, 28 Jan 2020 13:24:25 GMT

Thanks!

Re: Count the number of times a ky appeared individually

iSAS — Tue, 28 Jan 2020 13:25:02 GMT

Thanks!