How to pull weights of missing values from other column values?

[rohitdev_ds]

Hi,

I am trying to add last 2 columns in my data "Time_yrs_lower_limit' and "Time_yrs_higher_limit".

For doing this, here are the steps

1. Identify previous value of 'Time_yrs' for which 'Rate' value is not null

2. Put the value in cell "Time_yrs_lower_limit'

3.Identify next value of 'Time_yrs' for which 'Rate' value is not null

4. Put the value in cell "Time_yrs_higher_limit'

DATE	Time_yrs	Rate	Time_yrs_Lower_limit	Time_yrs_higher_limit
31-May-21	0.999315537	0.166285199
15-Mar-22	1.787816564		0.999315537	1.998631075
15-Apr-22	1.872689938		0.999315537	1.998631075
15-May-22	1.954825462		0.999315537	1.998631075
31-May-22	1.998631075	0.210218179

Here is the code

data have;
INFILE DATALINES DSD;
INPUT
DATE : DATE9.
Time_yrs
Rate
;
FORMAT DATE DATE9.
;
DATALINES;
31May2021, 0.999315537, 0.166285199
15Mar2022, 1.787816564,
15Apr2022, 1.872689938,
15May2022, 1.954825462,
31May2022, 1.998631075, 0.210218179 ,
;
run;

Thank you.

[Shmuel]

Try next not tested code:

proc sort data=have; by date; run;
data temp;
 set have;
     retain time_yrs_lower_limit;
	 if _N_ = 1 then output;
	 if not missing(rate) then 
	    time_yrs_lower_limit = time_yrs;
run;

proc sort data=have; by descending date; run;
data want;
 set temp;
     retain Time_yrs_higher_limit;
	 if _N_ = 1 then output;
	 if not missing(rate) then 
	    Time_yrs_higher_limit = time_yrs;
run;

[Shmuel]

I have tested and fixed my previous code and hope this is what you want:

data have;
	INFILE DATALINES DSD;
	INPUT DATE : DATE9. Time_yrs Rate;
	FORMAT DATE DATE9.;
DATALINES;
31May2021, 0.999315537, 0.166285199
15Mar2022, 1.787816564,
15Apr2022, 1.872689938,
15May2022, 1.954825462,
31May2022, 1.998631075, 0.210218179 ,
;
run;

proc sort data=have; by date; run;
data temp;
 set have;
     retain ID time_yrs_lower_limit;
     ID = _n_;
	 if rate then do; 
	    time_yrs_lower_limit=.; 
	    output; 
		time_yrs_lower_limit = time_yrs;
	 end;
	 else output;
run;

proc sort data=temp out=tmps; by descending ID ; run;
data want;
 set tmps;
     retain Time_yrs_higher_limit;
	 if rate then do; 
	    Time_yrs_higher_limit=.; 
	    output; 
		Time_yrs_higher_limit = time_yrs;
	 end;
	 else output;
run;

[Kurt_Bremser]

data have;
INFILE DATALINES DSD;
INPUT
DATE : DATE9.
Time_yrs
Rate
;
FORMAT DATE DATE9.
;
DATALINES;
31May2021, 0.999315537, 0.166285199
15Mar2022, 1.787816564,
15Apr2022, 1.872689938,
15May2022, 1.954825462,
31May2022, 1.998631075, 0.210218179 ,
;

data w1;
set have end=done;
retain Time_yrs_Lower_limit;
if rate ne . then Time_yrs_Lower_limit = .;
output;
if rate ne . then Time_yrs_Lower_limit = Time_yrs;
run;

proc sort data=w1;
by descending date;
run;

data want;
set w1 end=done;
retain Time_yrs_higher_limit;
if rate ne . then Time_yrs_higher_limit = .;
output;
if rate ne . then Time_yrs_higher_limit = Time_yrs;
run;

proc sort data=want;
by date;
run;

[rohitdev_ds]

thank you for your help.

[PhilC · Accepted solution]

My two cents.  I really thought this would be more straight forward when I started this.

PROC SQL;
  CREATE TABLE WORK.Want AS 
    SELECT 
      *, 
      ifn( missing(rate), MIN(Time_yrs), . ) AS Time_yrs_lower_limit, 
      ifn( missing(rate), Max(Time_yrs), . ) AS Time_yrs_higher_limit      
    FROM have
;
QUIT;

[rohitdev_ds]

Thank you Sir. This works.