topic Re: how to calculate average of duplicates across multiple rows and columns in SAS Programming

how to calculate average of duplicates across multiple rows and columns

yoyong555 — Thu, 30 May 2019 23:32:15 GMT

Hi everyone.

My data looks like the one below.

Date ID Trial1 Trial2 Trial3

Feb 1 A 74 71

Feb 10 A 71 72

Mar 5 B 76

Mar 12 C 78

Mar 23 C 79

Mar 30 C 80 80 80

I need to be able to produce an output like the one below showing the total number of trials per ID and average.

ID NTrials Mean

A 4 72

B 1 76

C 5 79.4

Thank you in advance.

Yoyong

Re: how to calculate average of duplicates across multiple rows and columns

Astounding — Fri, 31 May 2019 02:32:59 GMT

A relatively straightforward way:

data temp;
   set have;
   array trial {3};
   do k=1 to 3;
      if trial{k} > . then do;
         value = trial{k};
         output;
      end;
   end;
run;

proc summary data=temp nway;
   var value;
   class id;
   output out=want (drop=_type_ _freq_) n = NTrials mean = Mean;
run;