Manipulate long data

[amanda_cr]

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

[Astounding]

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.

[kiranv_]

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;

[amanda_cr]

Thanks everyone! All three solutions worked. I went with the SQL once because I prefer SQL.

[s_lassen · Accepted solution]

@amanda_cr:

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;

[amanda_cr]

Thank you!