SAS count observations based on condition

Question

I have two columns: member ID and member score. What I need to do is count the number of members if their score is greater than a specific value. I was thinking some sort of do over loop... For example, I want to count the number of members where score > 0.20 in increments of increasing the score by 0.01 until we end at count members where score > 0.40.

I was tinkering with arrays and do loops, but I can't figure it out. In the array I don't know if I can use something like

put nobs=; output;

Answer 1

You can use a second data set for your break point values and a SQL join to perform the criteria based counting.

Example:

data have;
  call streaminit(230911);
  do id = 1 to 1000;
    score =rand('uniform');
    output;
  end;
run;

data levels;
  do level = 0.20 to 0.40 by 0.025;
    output;
  end;
run;

proc sql; 
  create table counts as
    select level, count(distinct(id)) as count
    from have
    join levels
    on have.score > levels.level
    group by level
    ;
quit;

proc sgplot data=counts;
  series x=level y=count;
  label level = 'Score level';
run;  

Obs    level    count

 1     0.200     805 
 2     0.225     772 
 3     0.250     754 
 4     0.275     730 
 5     0.300     714 
 6     0.325     689 
 7     0.350     664 
 8     0.375     645 
 9     0.400     617 

Answer 2

You can also use a multidata format and Proc REPORT.

data format_data;
  fmtname = 'levels';
  end = constant('big');
  hlo = 'M'; 
  sexcl = 'Y'; /* exclude start value, i.e. > instead of >= */
  do start = 0.20 to 0.40 by 0.025;
    label = '> ' || put (start,5.3);
    output;
  end;
  hlo = 'O';
  label = 'Other';
  output;
run;
proc format cntlin=format_data;
run;

proc report data=have ;
  columns score score=scoreN ;
  define score / group order=data mlf preloadfmt format=levels.;
  define scoreN / 'Count' N ;
run;

score        Count
  > 0.200        805
  > 0.225        772
  > 0.250        754
  > 0.275        730
  > 0.300        714
  > 0.325        689
  > 0.350        664
  > 0.375        645
  > 0.400        617
  Other          195