topic Re: How to create an average of data in a datastep in SAS Programming

How to create an average of data in a datastep

sas_mania — Tue, 03 Jul 2018 09:33:22 GMT

Hi this is how my data looks and i want to include one more at bottom of dataset by taking an avg of last three records

pls suggest

DATA D01;
infile datalines missover;
param='weight(kg)';
INPUT USUBJID AVISIT $ AVISITN VISITNUM
VSSEQ @ 26 ABLFL $1. AVAL BASE CHG ;

DATALINES;
101 screening -4 1 1164 99 100
101 run-in -2 2 1165 101 100
101 baseline 0 3 1166 y 100 100 0
101 week24 24 4 1167 94 100 -6
101 week48 24 5 1168 92 100 -8
101 week52 52 6 1169 95 100 -5
;
run;

Re: How to create an average of data in a datastep

RW9 — Tue, 03 Jul 2018 09:46:34 GMT

Bit of a faff, but this works (note how I don't use uppercase coding and I use the code window - its the {i} above post area to retain indents and such like):

data d01;
  infile datalines missover;
  param='weight(kg)';
  input usubjid avisit $ avisitn visitnum vsseq ablfl aval base chg;
datalines;
101 screening -4 1 1164 99 100 
101 run-in -2 2 1165 101 100 
101 baseline 0 3 1166 y 100 100 0
101 week24 24 4 1167 94 100 -6
101 week48 24 5 1168 92 100 -8
101 week52 52 6 1169 95 100 -5
;
run;

proc sort data=d01 out=tmp;
  by usubjid descending avisitn;
run;

data tmp (drop=i tot);
  set tmp;
  retain i tot;
  by usubjid;
  i=ifn(first.usubjid,1,i+1);
  if i <= 3 then tot=sum(tot,aval);
  if last.usubjid then do;
    avisit="Average";
    avisitn=.;
    aval=tot/3;
    output;
  end;
run;

data want;
  set d01 tmp;
run;

Do note your output will always be 100 with that data.

Re: How to create an average of data in a datastep

Ksharp — Tue, 03 Jul 2018 12:23:21 GMT


data want;
 set d01;
 by usubjid;
 output;
 lag_chg=lag(chg);
 lag2_chg=lag2(chg);
 if last.usubjid then do;
   chg=mean(chg,lag_chg,lag2_chg);
   output;
 end;
 drop lag:;
run;