select the first variation and the last variation which exceeds the threshold

mazouz — Mon, 10 Aug 2020 13:43:15 GMT

I have this table: "Just for the exemple because in the reel problem I have more colomns and rows"

VARIABLE	Q1	Q2	Q3	Q4	Q5
Class1	12	14	134	1234	1234
Class2	1	4	6	34	56
Class3	3	4	4	4	8
Class4	2	12	12	45	156
Class5	12	22	24	45	45

I calculate variation between Q1 Q2 Q3 ...

VARIABLE	Q2/Q1	Q3/Q2	Q4/Q3	Q5/Q4
Class1	1,16666667	9,57142857	9,20895522	1
Class2	4	1,5	5,66666667	1,64705882
Class3	1,33333333	1	1	2
Class4	6	1	3,75	3,46666667
Class5	1,83333333	1,09090909	1,875	1

I want to creat table with just value of Q2 or Q3 or Q4 or Q5 when first or last variation Exceeding threshold=2

like this:

VARIABLE	First	Last	First_Q	Last_Q
Class1	134	1234	Q3	Q4
Class2	4	34	Q2	Q4
Class3	-	8	-	Q5
Class4	12	156	Q2	Q5
Class5	-	-	-	-

Re: select the first variation and the last variation which exceeds the threshold

Ksharp — Tue, 11 Aug 2020 13:12:43 GMT

data have;
infile cards expandtabs truncover;
input VAR $ Q1	Q2	Q3	Q4	Q5;
cards;
Class1	12	14	134	1234	1234
Class2	1	4	6	34	56
Class3	3	4	4	4	8
Class4	2	12	12	45	156
Class5 12	22	24	45	45
;

data temp;
 set have;
 array x{*} q1-q5;
 do i=1 to dim(x)-1;
  if x{i+1}/x{i} ge 2 then do;
    vname=vname(x{i+1});
	value=x{i+1};
	found=1;
	output;
  end;
 end;
 if not found then output;
 keep var vname value;
run;
data temp1;
 set temp;
 by var notsorted;
if not missing(vname) then do;
 if first.var then do;var_name='First';name='First_Q';end;
 if last.var then do;var_name='Last '; name='Last_Q ';end;
 if not missing(var_name) then output;
end;
else do;
 var_name='First';name='First_Q';output;
 var_name='Last '; name='Last_Q ';output;
end;
run;
proc transpose data=temp1 out=temp2(drop=_name_);
by var notsorted;
var value;
id var_name;
run;

proc transpose data=temp1 out=temp3(drop=_name_);
by var notsorted;
var vname;
id name;
run;

data want;
 merge temp2 temp3;
 run;

topic select the first variation and the last variation which exceeds the threshold in Statistical Procedures

select the first variation and the last variation which exceeds the threshold

Re: select the first variation and the last variation which exceeds the threshold