How to create a baseline value with missing values

[393310]

Hi all,  

I have data where in some records visit 1 is missing, meaning I need SAS to set the baseline value to the first observation that has an actual value. Here is my data +code:

data WORK.test;

input record visit orange apple;
CARDS;
1 1  1 .        
1 2  1 1
1 3  1 1
1 4  0 1
1 5  0 3
1 6  0 0 
1 7  0 0
2 1  2 .
2 2  0 .
2 3  0 1
2 4  0 0
;
run;
 
data test2;

 set test;

 by record visit;
 

 retain baseline_apple;
 

if (first.record) and apple ne . then  baseline_apple=apple; 

When I run this, it only sets baseline when the value isn't missing which is good, but I can't figure out how to tell it to use the next observation (if also not missing ) as the baseline and retain that baseline value for the rest of the visits.  (baseline gets set for each record ).

output: 

What I want is this: 

record	visit	apple	baseline
1	1	.	.
1	2	1	1
1	3	1	1
1	4	1	1
1	5	3	1
2	1	.	.
2	2	.	.
2	3	1	1

if I use "else baseline=apple" it won't retain the value across visits.

I dont wan't to use a statement like where apple ne . because it will delete the entire row and I could lose data for other variables in my data set.

Thank you!!

[Reeza]

 data test2;

 set test;

 by record visit;
 
 retain baseline_apple;
 
if first.record then call missing(baseline_apple);

if missing(baseline_apple) then baseline_apple = apple;

run;

I dont wan't to use a statement like where apple ne . because it will delete the entire row and I could lose data for other variables in my data set.

Thank you!!

---

I think you're referring to a subsetting IF? You can control the behaviour on the condition so it does not delete rows.

---

@393310 wrote:

Hi all,  

I have data where in some records visit 1 is missing, meaning I need SAS to set the baseline value to the first observation that has an actual value. Here is my data +code:

data WORK.test;

input record visit orange apple;
CARDS;
1 1  1 .        
1 2  1 1
1 3  1 1
1 4  0 1
1 5  0 3
1 6  0 0 
1 7  0 0
2 1  2 .
2 2  0 .
2 3  0 1
2 4  0 0
;
run;
 
data test2;

 set test;

 by record visit;
 

 retain baseline_apple;
 

if (first.record) and apple ne . then  baseline_apple=apple; 

When I run this, it only sets baseline when the value isn't missing which is good, but I can't figure out how to tell it to use the next observation (if also not missing ) as the baseline and retain that baseline value for the rest of the visits.  (baseline gets set for each record ).

output: 

What I want is this: 

record	visit	apple	baseline
1	1	.	.
1	2	1	1
1	3	1	1
1	4	1	1
1	5	3	1
2	1	.	.
2	2	.	.
2	3	1	1

if I use "else baseline=apple" it won't retain the value across visits.

I dont wan't to use a statement like where apple ne . because it will delete the entire row and I could lose data for other variables in my data set.

Thank you!!

---

[393310]

@Reeza 

That almost works except it does not retain the baseline across the rest of the visits. Any idea how I could get it to retain? 

output with your code:

[Reeza · Accepted solution]

Please show your code - that seems like the result without a RETAIN statement. 
Note that if you have baseline already in your data set and it's not a new variable you may also see this type of behaviour. 

But if you use your sample data and the code provided it seems to generate the exact results expected, is that correct?

data have;
infile cards dlm='09'x dsd truncover;
input record	visit	apple	baseline;
cards;
1	1	.	.
1	2	1	1
1	3	1	1
1	4	1	1
1	5	3	1
2	1	.	.
2	2	.	.
2	3	1	1
;;;
run;


data want;
set have;
by record visit;
retain baseline_apple;
if first.record then call missing(baseline_apple);

if missing(baseline_apple) then baseline_apple = apple;

run;

proc print;run;

---

@393310 wrote:

@Reeza 

That almost works except it does not retain the baseline across the rest of the visits. Any idea how I could get it to retain? 

output with your code:

---

[393310]

Sorry I must've left something out. I re ran again and it retained. Thank you so much!