How to count in proc sql/data step/ proc report

[SU22]

Hi,

I have a dataset as below:

Personnel number.      Code 1         Code 2         Code 3

1.                                   26

2                                     12                  26

3                                     15                                       12

4                                      26

5                                                           15

I want to write a code to return the # of times 26 or other codes appear.

Thanks for

[SASKiwi]

PROC FREQ is a good way of doing this:

proc freq data = have;
  table code1 * code2 * code3 / list missing;
run;

[Kurt_Bremser · Accepted solution]

proc transpose data=have out=long;
by personnel_number;
var code:;
run;

proc freq data=long;
tables col1;
run;

Maxim 19: Long Beats Wide.

[andreas_lds]

After transposing you could also use proc summary:

proc summary data=long nway;
   class Col1;
   output out=work.counted(drop= _type_ rename=(_freq_=Count));
run;

Or, if you love to write unnecessary long programs, use a hash-object:

data _null_;
   set have end=jobDone;
   
   length Code Count 8;
   
   array codes[3] Code1-Code3;
   
   if _n_ = 1 then do;
      declare hash h(ordered: 'yes');
      h.defineKey('Code');
      h.defineData('Code', 'Count');
      h.defineDone();
   end;
   
   do i = 1 to dim(codes);
      if not missing(codes[i]) then do;
         Code = codes[i];
         
         if h.find() = 0 then do;
            Count = Count + 1;
            h.replace();
         end;
         else do;
            Count = 1;
            h.add();
         end;
      end;
   end;
   
   if jobDone then do;
      h.output(dataset: 'work.hashCount');
   end;
run;