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;
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.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.