Do you mean this?
Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, max((test='SYS')*date) as max format=yymmdd10.
from new
group by subj;
quit;
Do you mean this?
Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, max((test='SYS')*date) as max format=yymmdd10.
from new
group by subj;
quit;
As @novinosrin asked not sure if you want the whole column set to the max date for "SYS", or if you just want a macro variable with the value.
The following creates a new column using a data step alternative:
data have;
input subj
test $
result
date yymmdd10.
;
format date date9.;
datalines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
/* use double DOW loop */
data want;
do until (last_check);
set have end = last_check;
if test eq 'SYS' then
new = max(new,date);
end;
format new date9.;
do until (last_obs);
set have end = last_obs;
output;
end;
run;
Amir.
What value should be in the new column for the rows that do not have a test value of 'SYS'?
Should they be blank or have the same value as the date column or something else?
Amir.
Good morning @sasuser123123 A simple tweak
Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, ifn(test='SYS',max((test='SYS')*date),.) as max format=yymmdd10.
from new
group by subj;
quit;
I should have said missing, rather than blank.
The following code sets the value to missing, which for numeric values is '.' by default, but if you really want a blank then you can comment out the options statement at the top to change the default from '.' to '', but the value will still be considered as missing.
/* change default numeric missing from '.' to '' */
*options missing = '';
/* use double DOW loop */
data want(drop = saved_new);
do until (last_check);
set have end = last_check;
if test eq 'SYS' then
new = max(new,date);
end;
format new date9.;
saved_new = new;
do until (last_obs);
set have end = last_obs;
if test ne 'SYS' then
call missing(new);
else
new = saved_new;
output;
end;
run;
Amir.
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.