Fluorite | Level 6

## imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

Hi everyone, my problem is a bit complex than that this simple example, but this example give you an idea what I wwant to achieve.

The idea is  imputed only the missing value of the last visits with LOCF, the missing intermediate visits remaining missing, and the carried baseline (i.e week2) if it is missing remaining missing.

In Bold face I show the visits need to be imputed with LOCF

data new;

input subjid week value;

datalines;

1  Baseline 10

1 Week2   12

1 week8  14

1 week12 16

2 baseline 12

2 week2   4

2 week8  14

3 Baseline 13

3 week4  5

3 week12   2

4 baseline 1

4 week2   3

5 Baseline 3

5 Week2  5

5 week12 6

run;

and I am looking for the want dataset:

1  Baseline 10

1 Week2   12

1 week8  14

1 week12 16

2 baseline 12

2 week2   4

2 week8  14

2 week12 14

3 Baseline 13

3 week4  5

3 week12   2

4 baseline 1

4 week2   3

4 week4   3

4 week8  3

4 week12 3

5 Baseline 3

5 Week2  5

5 week12 6

It is is a not easy problem I think, because you need to consider all the possible variations always keeepen imputed the last visits, and not the missing intermediate visisits, and the missing visits follow baseline.

Thnaks.

V.

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

I agree with Art on both issues but I will make a guess.
You want to create new observations, by the visit schedule up to week 12 if the LAST DOT observation for a subject is anything other than Baseline.
data new;

length subjid \$8 day \$10 value 8;

input subjid day :\$upcase. value;
* proper representation of time;

if day eq: 'B'

then time=0;

else time=input(substr(day,5),f8.);
datalines;
1 Baseline 10
1 Week2   12
1 week8  14
1 week12 16
2 baseline 12
2 week2   4
2 week8  14
3 Baseline 13
3 week4  5
3 week12   2
4 baseline 1
4 week2   3
5 Baseline 3
5 Week2  5
5 week12 6
6 Baseline 3
;;;;
run;
data locf;
set new;
by subjid;
output;

if last.subjid then do;

if time eq 0 then delete;
if time eq 2 then s=4; else s=time+4;

do time=s to 12 by 4;
day = cats(
'WEEK',time);
output;

end;

end;

drop s;
run;

 1 2 3 4 1 BASELINE 10 0 1 WEEK2 12 2 1 WEEK8 14 8 1 WEEK12 16 12 2 BASELINE 12 0 2 WEEK2 4 2 2 WEEK8 14 8 2 WEEK12 14 12 3 BASELINE 13 0 3 WEEK4 5 4 3 WEEK12 2 12 4 BASELINE 1 0 4 WEEK2 3 2 4 WEEK4 3 4 4 WEEK8 3 8 4 WEEK12 3 12 5 BASELINE 3 0 5 WEEK2 5 2 5 WEEK12 6 12 6 BASELINE 3 0

I forgot to create DAY for the LOCF obs.

8 REPLIES 8
Opal | Level 21

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

V.,

The goal of the forum is not to serve as free programmers but, rather, to help people learn the language so they can get to a level where they, too, can help new users.

I think you already have enough examples to at least try to solve this on your own.  If you run into a problem, post the code that you tried and explain where you want it to perform differently.

Fluorite | Level 6

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

Sorry, but  I am disagree, a forum is to learn from thisn, a problem generate new ideas.

The above example constitute the real problem, the above problem is the one  I want to achieve...is not fair saying I have enough examples to at least to solve the problem...I am trying to solve this problem from yesterday, is not an easy problem, because if not, it will be solved.

Best,

V.

Opal | Level 21

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

You haven't stated what the problem is that you are trying to solve.  I think your want dataset doesn't reflect what you really want.

My suggestion would be to define the rules you are trying to achieve and then try write the code that you think should solve that problem.

Many on the forum would be glad to assist at that point.

Fluorite | Level 6

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

Thank you Arthur.

I totally undertood your last email.

I will try next time.

Best,

V.

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

I agree with Art on both issues but I will make a guess.
You want to create new observations, by the visit schedule up to week 12 if the LAST DOT observation for a subject is anything other than Baseline.
data new;

length subjid \$8 day \$10 value 8;

input subjid day :\$upcase. value;
* proper representation of time;

if day eq: 'B'

then time=0;

else time=input(substr(day,5),f8.);
datalines;
1 Baseline 10
1 Week2   12
1 week8  14
1 week12 16
2 baseline 12
2 week2   4
2 week8  14
3 Baseline 13
3 week4  5
3 week12   2
4 baseline 1
4 week2   3
5 Baseline 3
5 Week2  5
5 week12 6
6 Baseline 3
;;;;
run;
data locf;
set new;
by subjid;
output;

if last.subjid then do;

if time eq 0 then delete;
if time eq 2 then s=4; else s=time+4;

do time=s to 12 by 4;
day = cats(
'WEEK',time);
output;

end;

end;

drop s;
run;

 1 2 3 4 1 BASELINE 10 0 1 WEEK2 12 2 1 WEEK8 14 8 1 WEEK12 16 12 2 BASELINE 12 0 2 WEEK2 4 2 2 WEEK8 14 8 2 WEEK12 14 12 3 BASELINE 13 0 3 WEEK4 5 4 3 WEEK12 2 12 4 BASELINE 1 0 4 WEEK2 3 2 4 WEEK4 3 4 4 WEEK8 3 8 4 WEEK12 3 12 5 BASELINE 3 0 5 WEEK2 5 2 5 WEEK12 6 12 6 BASELINE 3 0

I forgot to create DAY for the LOCF obs.

Fluorite | Level 6

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

Thnaks data null.

It looks nice.

Best,

V.

Fluorite | Level 6

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

I learn from you guys.

I will try my best next time.

These couple of days has been a nightmare for you (supporting my request) and for me, trying to solve my problem.

Now it has been solved, but not in the way I would like to, because I dont want to upset people.

Thanks again, and hat off to you all.

V.

Onyx | Level 15

## Re: imputed only the missing data of last visits (not intermediate missing visits and Baseline is not carried forward)

Hi V.,

No offense, but I do have a feeling of chasing my 5yr old inside a playground when trying to follow your thread. The forum is for helping each other, not for brain teasing (I don't mind a little bit of that though), so we do have a need to understand your purpose and get to the bottom of it. At the mean time, we expect you to pick it up as much as possible by studying the codes others have offered.  For instance, I mentioned that only one statement added to existing code will meet your need, I haven't seen your response yet: https://communities.sas.com/thread/38368

For this new requirement, my approach will be 2XDOW plus Ksharp's logic :

data new;

input subjid day\$. value;

datalines;

1 Baseline 10

1 Week2 12

1 week8 14

1 week12 16

2 baseline 12

2 week2 4

2 week8 14

3 Baseline 13

3 week4 5

3 week12 2

4 baseline 1

4 week2 3

5 Baseline 3

5 Week2 5

5 week12 6

;

data want;

array a{4} \$ _temporary_ ('week2' 'week4' 'week8' 'week12');

do until (last.subjid);

set new;

by subjid;

end;

if day='week12' then _flag=1;

do until (last.subjid);

set new;

by subjid;

set new (firstobs=2 keep=day rename=( day=_day)) new(obs=1 drop=_all_);

output;

if day =: 'week' and _flag ne 1 then do;

_end=whichc(_day,of a{*});

do _i=whichc(day,of a{*})+1 to ifn(_end=0,dim(a),_end-1);

day=a{_i}; output;

end;

end;

end;

drop _:;

run;

proc print;run;

Haikuo

Discussion stats
• 8 replies
• 1093 views
• 6 likes
• 4 in conversation