CODEWITHSUNDEEP
countsas
Stefano Lombardi
Stefano Lombardi

Reputation: 1591

Counting the repeated values of a variable by id

I am at my very first steps with SAS, and I incurred into the following problem which I am not able to solve.

Suppose my dataset is:

data dat;
  input id score gender;
  cards;
  1 10 1
  1 10 1
  1 9 1
  1 9 1
  1 9 1
  1 8 1
  2 9 2
  2 8 2
  2 9 2
  2 9 2
  2 10 2
  ;
run;

What I need to do is to count the number of times the score variable takes values 8, 9 and 10 by id. Then to create the newly variables count8, count9 and count10 such that I can get the following output:

  id     gender    count8    count9    count10
   1        1         1          3        2
   2        2         1          3        1

How would you suggest ot proceed? Any help would be greatly appreciated.

Upvotes: 0

Views: 1138

Answers (1)

Joe
Joe

Reputation: 63424

Lots of ways to do that. Here's a simple one data step approach.

data want;
set dat;
by id;
if first.id then do;
  count8=0;
  count9=0;
  count10=0;
end;
select(score);
  when(8) count8+1;
  when(9) count9+1;
  when(10) count10+1;
  otherwise;
end;
if last.id then output;
keep id count8 count9 count10;
run;

SELECT...WHEN is a shortening of a bunch of IF statements, basically (like CASE..WHEN in other languages).

Gender should be dropped, by the way, unless it's always the same by ID (or unless you intend to count by it.)

A more flexible approach than this is to use a PROC FREQ (or PROC MEANS or ...) and transpose it:

proc freq data=dat noprint;
tables id*score/out=want_pre;
run;

proc transpose data=want_pre out=want prefix=count;
by id;
id score;
var count;
run;

If you really only want 8,9,10 and want to drop records less than 8, do so in the data=dat part of PROC FREQ:

proc freq data=dat(where=(score ge 8)) noprint;

Upvotes: 3

Related Questions