Help using Base SAS procedures

Creating a new variable that counts number of occurences

Reply
Occasional Contributor
Posts: 14

Creating a new variable that counts number of occurences

Hi all,

I have a set of survey data which gives each person's personal identification code (lets call this variable ID for now). Most people were called more than once, and each time they were called, they are added as a separate entry in the data set, therefore there are many duplicate personal identification codes, but with different values for other variables (which are the questions they were asked).

I collapsed the ID variable so each unique id only shows up once in the data set (I am creating new summary variables and dropping all other variables in the original data set). I would like to create a variable that sums the number of times the ID was duplicated to show the number of times the person was called. Does anyone know how to do this?

Basically I want this new variable to be what a proc freq on the ID variable would reveal, and I will create it before I collapse the ID variable.

I can't use first.ID/last.ID because it requires me to collapse the ID variable first.

Thanks in advance for the help!

Super User
Posts: 19,862

Re: Creating a new variable that counts number of occurences

Use a SQL query - this will work in SAS but isn't ANSI SQL, so won't work in other SQL systems.

proc sql;

create table want as

select *, count(id) as Num_ID

from have

group by ID

order by ID;

quit;

Occasional Contributor
Posts: 14

Re: Creating a new variable that counts number of occurences

Thanks so much, it worked!!

Ask a Question
Discussion stats
  • 2 replies
  • 1622 views
  • 0 likes
  • 2 in conversation