Hi @Kurt_Bremser @FreelanceReinhard , and @smantha
Thank you very much for your discussion and contribution, I benefit a lot from your conversation and codes as well.
After adding an update from @FreelanceReinhard to @smantha 's solution (because I agreed that @Kurt_Bremser 's former solution created too many new numeric variables), and change last.type to last.date (I thought it is incorrect if we put the last.date there because we did not set BY group for last date previously, so I deem it is your mistype, am I correct, @smantha ?, please let me know if my adjustment is wrong).
So, the final code seems to be as below, please let me know if you think it is incorrect or else that I can close the topic. Once again, many thanks!
data want;
set have;
by type;
array _date(11712) _temporary_;
array _r(11712) _temporary_;
retain _date: _r: start;
if first.type then do;
*I updated FreelanceReinhard's solution here;
call missing(of _date[*]);
call missing(of _r[*]);
start = 0;
end;
start = start+1;
_date[start]=date;
_r[start] = r;
*I changed last.date to last.type here;
if last.type then do;
do i = 1 to start;
if i > 1 then do;
if ((1 + _r[i]/100)*( 1 +_r[i-1]/100) -1) <= 0.5 and (_r[i] > 100 or _r[i-1] > 100) then do;
r=.;
date = _date[i];
output;
end;
else do;
r=_r[i];
date = _date[i];
output;
end;
end;
else do;
r=_r[i];
date = _date[i];
output;
end;
end;
end;
run;
Yes the code looks good to me. I learnt a lot too (the least important of which is never type code in an iphone)!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: