Create one line from several lines (not transpose)

[lansoprazole]

Hi all,

I have this table 

ID	Var1	Var2
1	.	6
1	2	8
1	.	9
2	.	1
2	.	3
2	.	5

I am trying to create a new dataset with ONE LINE for each ID and a new var (Var3) that its value depends on Var1 and Var2:

- If Var1 is missing, then Var3 = last Var 2 value ; else Var3 = Var1 

(Var3 for each ID will be either first non-missing Var1 or last Var2 value)

So the new dataset would look like

ID	Var3
1	2
2	5

Thanks,

W

[Shmuel]

data want;
 set have;
 by ID;
      retain var3;
      keep ID var3;
      if first.ID then var3 = .;
      if var1 then var3 = var1;
      if last.id then do;
         if var1 = . then do; var3=var2; output; end;
         else output;
     end;
run;
   

[lansoprazole]

Thanks for your reply.

I believe there should be an output statement once var3 takes the value of var1 and move to the second ID.

I tried your program but the result was:

ID Var3

1   9

2   5

[Tom · Accepted solution]

You can use the UPDATE statement for this problem.

data want;
   update have(obs=0) have;
   by id;
   var3 = coalesce(var1,var2);
run;

If you don't want the values of VAR2 retained (ie if VAR1 is all missing and the LAST value of VAR2 is missing means that then you want VAR3 to be missing even if earlier values of VAR2 were non-missing) then add an extra SET statement to re-read just VAR2 to replace any retained value.

data want;
   update have(obs=0) have;
   by id;
  set have(keep=var2);
   var3 = coalesce(var1,var2);
run;

[lansoprazole]

Thanks!! 

That worked. 

W