How to do a last observation carrying forward using SAS PROC SQL

Question

I have the data below. I want to write a sas proc sql code to get the last non-missing values for each patient(ptno).

data sda;
input ptno visit weight;
format ptno z3. ;
cards;
1 1 122
1 2 123
1 3 .
1 4 .
2 1 156
2 2 .
2 3 70
2 4 .
3 1 60
3 2 .
3 3 112
3 4 .
;
run;

proc sql noprint;
create table new as
select ptno,visit,weight,
case 
                when weight = . then weight
                else .
                end as _weight_1
      from sda
group by ptno,visit
order by ptno,visit;
quit;

The sql code above does not work well.

The desire output data like this:

    ptno visit weight
    1      1    122
    1      2    123
    1      3    123
    1      4    123
    2      1    156
    2      2    .
    2      3    70
    2      4    70
    3      1    60
    3      2    .
    3      3    112
    3      4    112

Answer 1

Since you do have effectively a row number (visit), you can do this - though it's much slower than the data step.

Here it is, broken out into a separate column for demonstration purposes - of course in your case you will want to coalesce this into one column.

Basically, you need a subquery that determines the maximum visit number less than the current one that does have a legitimate weight count, and then join that to the table to get the weight.

proc sql;
  select ptno, visit, weight, 
    (
        select weight 
            from sda A,
            (select ptno, max(visit) as visit
                from sda D
                where D.ptno=S.ptno
                and D.visit<S.visit
                and D.weight is not null
                group by ptno
            ) V
            where A.visit=V.visit and A.ptno=V.ptno
    )
    from sda S
    ;
quit;

Answer 2

Although you don't describe it that way you do not carry forward VISIT 1 right?

I don't know why you would want to do this using SQL. In SAS a data step is much better suited to the task. I like using the "update trick". If you're interested in how this works I will leave it to you to study the UPDATE statement.

data locf;
   update sda(obs=0 keep=ptno) sda;
   by ptno;
   output;
   if visit eq 1 then call missing(weight);
   run;