topic Re: Longitudinal data - flag missing yet keeping available info in SAS Programming

Longitudinal data - flag missing yet keeping available info

Cruise — Mon, 10 Sep 2018 13:49:00 GMT

I have patients test values recorded at their each of visits (n_visits). There are missing in test values in different patterns. I want to keep all the available information while removing rows with missing while tracking for patients who had no test value at all visits (patient 4 and 6).

What would be a solution to flag patients 4 and 6 to indicate that they had no test values at all while keeping other patients information yet getting rid of missing rows?

Thanks!!!!

data have;
input patient_id n_visits value value_now;
cards;
1 1 22 21
1 2 23 23
1 3 . .
1 4 . .
2 1 . .
2 2 . .
2 3 56 .
2 4 . .
2 5 . .
3 1 23 .
3 2 . .
3 3 . .
4 1 . .
5 1 22 .
6 1 . .
6 2 . .
6 3 . .
;
data want;
input patient_id n_visits value value_now flag_missing;
cards;
1 1 22 21 0
1 2 23 23 0
2 3 56 . 0
3 1 23 . 0
4 1 . . 1
5 1 22 . 0
6 1 . . 1
;

Re: Longitudinal data - flag missing yet keeping available info

Ksharp — Mon, 10 Sep 2018 14:42:56 GMT

Assuming I understood what you mean.

data have;
input patient_id n_visits value value_now;
cards;
1 1 22 21
1 2 23 23
1 3 . .
1 4 . .
2 1 . .
2 2 . .
2 3 56 .
2 4 . .
2 5 . .
3 1 23 .
3 2 . .
3 3 . .
4 1 . .
5 1 22 .
6 1 . .
6 2 . .
6 3 . .
;
proc sql;
create table temp as
select *
 from have
  where value is not missing or value_now is not missing;
create table missing as
 select * from have where  patient_id not in (select patient_id from temp)
  order by 1,2;
quit;
data missing_first;
 set missing;
 by patient_id;
 if first.patient_id;
run;
data want;
 set temp missing_first;
 by patient_id n_visits;
run;

Re: Longitudinal data - flag missing yet keeping available info

mkeintz — Mon, 10 Sep 2018 14:45:42 GMT

You want to delete all records with value missing, EXCEPT keep one record for any patient with all missing values. This program does that:

data have;
input patient_id n_visits value value_now;
cards;
1 1 22 21
1 2 23 23
1 3 . .
1 4 . .
2 1 . .
2 2 . .
2 3 56 .
2 4 . .
2 5 . .
3 1 23 .
3 2 . .
3 3 . .
4 1 . .
5 1 22 .
6 1 . .
6 2 . .
6 3 . .
;
data want (drop=_:);
  set have;
  by patient_id;
  if first.patient_id then _nzeroes=0;
  if value^=. then flag=0;
  _nzeroes+(flag=0);
  if last.patient_id and _nzeroes=0 then flag=1;
  if flag=1 or flag=0;  
run;

However, instead of keeping the first record for id 6, this keeps the last - which you might actually prefer, since n_visit becomes the number of visit records for that patient. But if you actually want to keep the first record:

data have;
input patient_id n_visits value value_now;
cards;
1 1 22 21
1 2 23 23
1 3 . .
1 4 . .
2 1 . .
2 2 . .
2 3 56 .
2 4 . .
2 5 . .
3 1 23 .
3 2 . .
3 3 . .
4 1 . .
5 1 22 .
6 1 . .
6 2 . .
6 3 . .
;
data want (drop=_:);
  set have;
  by patient_id;
  if first.patient_id then do;
    _nzeroes=0;
    _firstrec=_n_;
  end;
  if value^=. then flag=0;
  _nzeroes+(flag=0);
  if last.patient_id and _nzeroes=0 then do;
    set have point=_firstrec;
    flag=1;
  end;
  if flag=1 or flag=0;  
run;

Re: Longitudinal data - flag missing yet keeping available info

novinosrin — Mon, 10 Sep 2018 15:34:50 GMT

data have;
input patient_id n_visits value value_now;
cards;
1 1 22 21
1 2 23 23
1 3 . .
1 4 . .
2 1 . .
2 2 . .
2 3 56 .
2 4 . .
2 5 . .
3 1 23 .
3 2 . .
3 3 . .
4 1 . .
5 1 22 .
6 1 . .
6 2 . .
6 3 . .
;

proc sql;
create table want(drop=r) as
select *,missing(value) as flag_missing,range(calculated flag_missing) as r
from have
group by patient_id
having ((r=0)*n_visits)=min(n_visits) or (value ne .)
order by patient_id,n_visits;
quit;