topic How to create a baseline value with missing values in SAS Programming

How to create a baseline value with missing values

393310 — Thu, 18 Nov 2021 16:00:53 GMT

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!!

Re: How to create a baseline value with missing values

Reeza — Thu, 18 Nov 2021 16:36:09 GMT

 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!!

Re: How to create a baseline value with missing values

393310 — Thu, 18 Nov 2021 17:03:41 GMT

@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:

Re: How to create a baseline value with missing values

Reeza — Thu, 18 Nov 2021 17:14:01 GMT

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:

Re: How to create a baseline value with missing values

393310 — Thu, 18 Nov 2021 17:25:45 GMT

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