complete blank records with the last period until new period started

[cuan]

Hi guys,

I hope you can help me with this problem.

subject daily visit

101 11aug2021 period 1

101 12 aug2021 period 1

101  13aug2021 ---------> period 1

101 14aug2021 ----------> period 1

101 15 aug2021 period 2

101 16 aug2021 period 2

101 17 aug2021 period 2

101 18 aug2021 -------> period 2

101 19 aug2021 period 3

102 11aug2021 period 1

102 12 aug2021 period 1

102  13aug2021 -------->period 1

102 14aug2021. -------->period 1

102 15 aug2021 period 2

102 16 aug2021 period 2

102 17 aug2021 period 2

102 18 aug2021 ------> period 2

102 19 aug2021 period 3

How can i complete period 1 days 13 and 14, and period 2 day 18,

basically the blank records (arrow) need to get the value of  the previous period until new period start.

Thanks in advance.

Cuan.

[ballardw · Accepted solution]

Basic approach is to create a new variable that you assign the value to and retain it so it is available later.

Untested code as I didn't feel like creating a data step:

data want;
   set have;
   length holdvisit $ 10; /*<= length should match your exisiting visit value*/
   retain holdvisit;
   if not missing(visit) then holdvisit=visit;
   if missing(visit) then visit=holdvisit;
   /*drop holdvisit; */
run;

Have would be your existing data. The missing function returns true/false (actually 1/0 but used that way) for you existing visit variable so you conditionally assign the retained value. Retain will keep the previous value from a step iteration. After checking that this is working as intended you may want to drop the retained variable Holdvisit.

Caution: if any of your SUBJECT values do not have a Visit assigned for the first "visit" this will not work as will assign the last of the previous subject. But you didn't provide that case as an example. It is also a nastier problem.

[AMSAS]

RETAIN Statement is the way to go