topic Re: Adding a new column to a sas table with the number of records in each group in SAS Programming

Adding a new column to a sas table with the number of records in each group

aalluru — Tue, 24 Nov 2020 15:22:03 GMT

Suppose I have a SAS table as follows:

ID Yr

A 2003

A 2004

B 2003

I would like to add a new column which has the number of occurrences of each ID as follows:

ID Yr Occurences

A 2003 3

A 2004 3

B 2003 2

Please advise on how I can do this

Re: Adding a new column to a sas table with the number of records in each group

novinosrin — Tue, 24 Nov 2020 15:31:13 GMT



data have;
input ID $   Yr;
cards;
A    2003

A    2004

A    2004

B    2003

B    2003
;

proc sql;
 create table want as
 select *, count(*) as occurences
 from have
 group by id
 order by id, yr;
quit;

Re: Adding a new column to a sas table with the number of records in each group

novinosrin — Tue, 24 Nov 2020 15:30:00 GMT

data want;
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
  occurences=sum(occurences,1);
 end;
 do _n_=1 to _n_;
  set have;
  output;
 end;
run;

Re: Adding a new column to a sas table with the number of records in each group

aalluru — Tue, 24 Nov 2020 15:33:51 GMT

That worked perfectly. Thank you so much!