Hi, everybody!
I have a variable which must be descending, but I have some values that brokes this rule. For example:
Have
100
90
85
80
90 <---
70
90 <---
80 <---
75 <---
60
50
For these guys, I want substitute in a new variable for missing. This way:
Have Want
100 100
90 90
85 85
80 80
90 <--- .
70 70
90 <--- .
80 <--- .
75 <--- .
60 60
50 50
I worked hard, but I couldn't. Anyone can help me?
Thanks!
data have;
input n;
cards;
100
90
85
80
90
70
90
80
75
60
50
;
data temp;
set have;
retain want;
if _n_=1 then want=n;
else if n<want then want=n;
run;
data want;
set temp ;
by want notsorted;
if not first.want then call missing(want);
run;
One way:
data new;
set old;
if _n_=1 then last_valid = have;
retain last_valid;
if have <= last_valid then do;
last_valid = have;
want = have;
end;
run;
It's untested code, so you will have to see if it works for you.
Use the lag() and ifn() functions:
want = ifn(have > lag(have) and _n_ ne 1,.,have);
Edit: took care of the first data step iteration (thanks to @Astounding for bringing that to my attention).
data have;
input n;
cards;
100
90
85
80
90
70
90
80
75
60
50
;
data temp;
set have;
retain want;
if _n_=1 then want=n;
else if n<want then want=n;
run;
data want;
set temp ;
by want notsorted;
if not first.want then call missing(want);
run;
data have;
input n;
cards;
100
90
85
80
90
70
90
80
75
60
50
;
data want;
set have;
retain min 99999;
min=min(min,n);
if min<n then call missing(n);
drop min;
run;
data have;
input n;
cards;
100
90
85
80
90
70
90
80
75
60
50
;
data want;
set have;
retain min 99999;
min=min(min,n);
if min<n then call missing(n);
drop min;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.