How to generate the variable "frequency"

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

How to generate the variable "frequency"

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!

Attachment

Accepted Solutions
Solution
‎04-19-2014 09:55 AM
Super User
Posts: 10,018

Re: How to generate the variable "frequency"

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

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: How to generate the variable "frequency"

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

Frequent Contributor
Posts: 130

Re: How to generate the variable "frequency"

Posted in reply to RichardinOz

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:

frequency.png

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

results.png

Solution
‎04-19-2014 09:55 AM
Super User
Posts: 10,018

Re: How to generate the variable "frequency"

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

Frequent Contributor
Posts: 130

Re: How to generate the variable "frequency"

That works. Thank you, Keshan!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 7014 views
  • 3 likes
  • 3 in conversation