topic Re: pick the last non-missing value between two variables by the third variable in SAS Programming

pick the last non-missing value between two variables by the third variable

CHL0320 — Tue, 07 Jan 2020 16:53:04 GMT

Hi,

A SAS rookie needs help in creating a new variable base on the following rule.

1. If a or b is not missing at any visits, then d is equal to the last non-missing values between a and b whichever is greater.

2. if a and b are missing at all visits then d is equal to the last non-missing value of c.

For example, Data like is

ID	a	b	visit	c
1		1.25	6	3.255
1			7
1	3.00		8	8.515
1			9
2			6	1.512
2			7	6.608
2			8
2			9	13.381
3			6	2.729
3			7	5.951
3			8	8.515
3			9	11.737
4	0.25	1.00	6	3.321
4			7	6.97
4			8	8.515
4			9	13.545

Data want is

ID	a	b	visit	c	d
1	3.00		8	8.515	3.00
2			9	13.381	13.381
3			9	11.737	11.737
4	0.25	1.00	6	3.321	1.00

Re: pick the last non-missing value between two variables by the third variable

novinosrin — Tue, 07 Jan 2020 18:17:32 GMT

Hi @CHL0320 If I understand you correctly, it's pretty straight forward double DOW



data have;
infile cards truncover;
input ID	a	b	visit	c;
cards;
1	.	1.25	6	3.255
1	.	.	7	.
1	3	.	8	8.515
1	.	.	9	
2	.	.	6	1.512
2	.	.	7	6.608
2	.	.	8	
2	.	.	9	13.381
3	.	.	6	2.729
3	.	.	7	5.951
3	.	.	8	8.515
3	.	.	9	11.737
4	0.25	1	6	3.321
4	.	.	7	6.97
4	.	.	8	8.515
4	.	.	9	13.545
;

data want;
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
  t=max(a,b);
  if t then do; t1=visit;t2=t;end;
  if c then do; c1=c;c2=visit;end;
 end;
 do _n_=1 to _n_;
  set have;
  if t2 then if t1=visit then do;
   d=t2;
   output;
  end;
  if t2=. and c2=visit then do;
   d=c1;
   output;
  end;
 end;
 drop c1 c2 t:;
run;

Re: pick the last non-missing value between two variables by the third variable

Ksharp — Wed, 08 Jan 2020 12:30:29 GMT


data have;
infile cards truncover expandtabs;
input ID	a	b	visit	c;
cards;
1	.	1.25	6	3.255
1	.	.	7	.
1	3	.	8	8.515
1	.	.	9	
2	.	.	6	1.512
2	.	.	7	6.608
2	.	.	8	
2	.	.	9	13.381
3	.	.	6	2.729
3	.	.	7	5.951
3	.	.	8	8.515
3	.	.	9	11.737
4	0.25	1	6	3.321
4	.	.	7	6.97
4	.	.	8	8.515
4	.	.	9	13.545
;

data want;
 do i=1 by 1 until(last.id);
  set have;
  by id;
  if not missing(a) or not missing(b) then idx=i;
 end;
 if missing(idx) then do;flag=1;idx=i;end;
 do j=1 by 1 until(last.id);
  set have;
  by id;
  if j<=idx and not missing(c) then do; 
   _a=a;_b=b;_visit=visit;_c=c;
   if flag then d=c;
     else d=coalesce(b,a);
  end;
 end;
 keep id _: d;
 run;