topic Re: Calculate differences and mean between rows with same suffix in New SAS User

Calculate differences and mean between rows with same suffix

bkq32 — Tue, 26 Nov 2019 20:09:15 GMT

I started with two SAS data sets (reader + gold standard) with several body measurement values, added prefixes to differentiate which data set they came from, and merged them by ID to get something like:

data have;
 input ID $4. READER_ARM READER_LEG GOLD_ARM GOLD_LEG ;
 cards;
A123 5 8 2 6 9 2
B543 1 6 2 3 4 4
C859 4 7 2 5 8 4
;
run;

I'd like to calculate the difference and mean between variables with the same suffix to end up with:

data want;
 set have;

 DIFF_ARM=READER_ARM-GOLD_ARM;
 DIFF_LEG=READER_LEG-GOLD_LEG;

 MEAN_ARM=(READER_ARM+GOLD_ARM)/2;
 MEAN_LEG=(READER_LEG+GOLD_LEG)/2;

run;

Since my actual data set has many body measurement variables, how can I simplify this with an array or macro? I can rename the variables too if that makes it easier.

Re: Calculate differences and mean between rows with same suffix

PaigeMiller — Tue, 26 Nov 2019 20:22:41 GMT

If you know the list of suffixes in advance, you could easily write a macro to do this. (Do you know the list of suffixes in advance?)

UNTESTED CODE

%macro dothis(suffixes=);
    %let nsuffix=%sysfunc(countw(&suffixes));
    data want;
        set have;
        %do i=1 %to &nsuffix;
             %let thissuffix=%scan(&suffixes,&i,%str( ));
             diff_&thissuffix=sum(reader_&thissuffix,-gold_&thissuffix);
             mean_&thissuffix=mean(read_&thissuffix,gold_&thissuffix);
         %end;
    run;
%mend;
%dothis(suffixes=ARM LEG)

Re: Calculate differences and mean between rows with same suffix

bkq32 — Tue, 26 Nov 2019 20:34:36 GMT

Yes I do, and that worked perfectly. Thanks, Paige!