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.
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;
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;
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;
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;
thank you for your help.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.