Hi,
Here is how my data looks:
ID Type
1 4045
1 0014
1 0100
1 0038
2 0014
2 4045
2 0006
I want to identify all IDs where Type=0100 (this means the procedure did not run so the other types aren't accurate), while keeping the data the same when there is no Type 0100 for the ID.
So in this case my data would look like this:
ID Type2
1 procedure didn't run
2 0014
2 4045
2 0006
How would I do this? I'm thinking it may have to do with using first and last.
AC
The simples solution I can think of is this:
data want; merge have have(where=(type='0100') in=failed); by ID; if failed then do; if first.ID; type='Procedure did not run'; end; run;
Here's one way:
data want;
length type $ 20;
do until (last.id);
set have;
by id;
if type = '0100' then situation = "procedure didn't run";
end;
do until (last.id);
set have;
by id;
if situation = ' ' then output;
end;
if situation = "procedure didn't run" then do;
type = situation;
output;
end;
drop situation;
run;
This assumes that your data set is sorted by ID. The top loop examines observations for one ID, to see if "0100" is found. The second loop re-reads the same observations, possibly outputting them.
Below query should work.
data have; input ID Type $; datalines; 1 4045 1 0014 1 0100 1 0038 2 0014 2 4045 2 0006 ; proc sql; create table want as select distinct id, type2 from (select id, case when id in (select id from have a where trim(Type) ='0100') then 'procedure did not run' else Type end as Type2 from have)a; quit;
Thanks everyone! All three solutions worked. I went with the SQL once because I prefer SQL.
The simples solution I can think of is this:
data want; merge have have(where=(type='0100') in=failed); by ID; if failed then do; if first.ID; type='Procedure did not run'; end; run;
Thank you!
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 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: