topic Re: How to generate the variable "frequency" in SAS Programming

How to generate the variable "frequency"

Jonate_H — Sat, 19 Apr 2014 04:28:56 GMT

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!

Re: How to generate the variable "frequency"

RichardinOz — Sat, 19 Apr 2014 06:18:50 GMT

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 ;

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

Re: How to generate the variable "frequency"

Jonate_H — Sat, 19 Apr 2014 13:47:39 GMT

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:

Re: How to generate the variable "frequency"

Ksharp — Sat, 19 Apr 2014 13:55:22 GMT

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

Re: How to generate the variable "frequency"

Jonate_H — Sat, 19 Apr 2014 22:58:01 GMT

That works. Thank you, Keshan!