topic Re: How to count in proc sql/data step/ proc report in SAS Programming

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

SU22 — Fri, 04 Mar 2022 02:56:40 GMT

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

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

SASKiwi — Fri, 04 Mar 2022 03:07:55 GMT

PROC FREQ is a good way of doing this:

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

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

Kurt_Bremser — Fri, 04 Mar 2022 06:38:58 GMT

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

proc freq data=long;
tables col1;
run;

Maxim 19: Long Beats Wide.

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

andreas_lds — Fri, 04 Mar 2022 07:04:33 GMT

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;