How to generate the variable "frequency"

[Jonate_H]

For the attached sample data, I would like to generate the variable "frequency", its value is calculated by id and by year. what is the simply way to get that? I know that the simple way to get the variable "count" is something like:

data want;

  set sampledata;

  count + 1;

  by id year;

  if first.id or first.year then count = 1;

run;

Thanks!

[RichardinOz]

You can do counts in SQL

Proc SQL ;

     Create table want as

          select id

               ,     year

               ,     count (id) as count

          From have

          Group by id, year

     ;

Quit ;

Or

Proc Freq data = have ;

     tables id * year  / out = want (drop = percent) ;

run ;

You could also use Summary or Univariate if there is another numeric variable you can use for counting (use the N statistic).

Proc Report or Tabulate are also options

Richard

[Jonate_H]

Thank you Richard!

In that way, I will have a summarized table as below first image shows.

so, in order to make the exact same data set as below second image shows, I will have to merge original data with the summarized table.

Is it possible that I can create the variable "frequency" without using merge?

This is the table I get by Richard's first code:

I hope to create the variable "frequency" directly in the original data set as below showed:

[Ksharp · Accepted solution]

Add some other variables other than group variable into sql.

Proc SQL ;

     Create table want as

          select *

               ,     count (id) as count

          From have

          Group by id, year

     ;

Quit ;

Xia Keshan

[Jonate_H]

That works. Thank you, Keshan!