calculate the change from minimum of the previous visit values

[Jagadishkatam]

Hi experts,

This is with regard to a programming query to calculate the change from minimum of the previous visit values.

The calculation would check each visit against all prior visits for smallest value and calculate a percentage of change. An example would be something like visit 3 checks visit 1 and 2 and get the minimum of visit 1 and visit 2 to derive the change. I tried the below code in datastep and i am getting the expected output.

Would like to check if there are any better approaches in datastep or proc sql

Subjid Visit	Value
6	Visit1	75
6	Visit2	82
6	Visit3	14
6	Visit4	73
6	Visit5	67
6	Visit6	61
6	Visit7	38


proc sort data=have out=have2(keep=subjid visit value);
by subjid visit ;
run;

data want;
length new new2 $100;
set have2;
by subjid visit;
retain new;
if first.subjid then new='';
if value ne . then new=catx(',',new,value);
new2=lag(new);
if first.subjid then new2='';
array cnt(15) x1-x15;
do i=1 to 15;
cnt(i)=input(scan(new2,i,','),best.);
end;
base=min(of x1-x15);
if nmiss(value,base)=0 then pchg=((value-base)/base)*100;
drop new new2 x: i;
run;


Subjid Visit Value BASE PCHG
6	Visit1	75	.	.
6	Visit2	82	75	7
6	Visit3	14	75	-61
6	Visit4	73	14	59
6	Visit5	67	73	-6
6	Visit6	61	67	-6
6	Visit7	38	61	-23

[FreelanceReinh · Accepted solution]

Hi Jag,

Try this (using your sorted dataset HAVE2):

data want;
do until(last.subjid);
  set have2;
  by subjid visit;
  if nmiss(value,base)=0 then pchg=((value-base)/base)*100;
  else pchg=.;
  output;
  if value>.z then base=min(base, value);
end;
run;

Actually, I would prefer a numeric variable containing the visit number so as to avoid incorrect sort orders like 'visit10'<'visit2'.

Edit: Assuming that all non-missing VALUEs are positive.

Edit 2: Inserted "if value>.z then " just to avoid a note in the log about "... performing an operation on missing values" in cases where the first VALUE in a BY group is missing.

[Jagadishkatam]

Thank you @FreelanceReinh for your response and the code gave the expected result.

I agree with you to have a numeric variable for sorting purpose.

data have;
infile cards dlm='09'x;
input Subjid : Visit$	Value;
visitnum=input(compress(visit,,'kd'),best.);
cards;
6	Visit1	75
6	Visit2	82
6	Visit3	14
6	Visit4	73
6	Visit5	67
6	Visit6	61
6	Visit7	38
;

proc sort data=have out=have2(keep=subjid visit visitnum value);
by subjid visitnum ;
run;

data want;
do until(last.subjid);
  set have2;
  by subjid visitnum;
  if nmiss(value,base)=0 then pchg=((value-base)/base)*100;
  else pchg=.;
  output;
  if value>.z then base=min(base, value);
end;
run;

[Ksharp]

Why Visit 5 6 7 is not 14?

data have;
infile cards expandtabs truncover;
input Subjid Visit $ Value;
cards;
6	Visit1	75
6	Visit2	82
6	Visit3	14
6	Visit4	73
6	Visit5	67
6	Visit6	61
6	Visit7	38
;
run;
data want;
  set have;
  by subjid;
array x{999} _temporary_;
if first.subjid then do;n=0; call missing(of x{*});	end;
base=min(of x{*});
n+1;x{n}=value;
drop n;
run;

[Jagadishkatam]

Thank you @Ksharp, My mistake it should be 14 on visit 5,6,7

You solution also worked.