topic Re: Manipulate long data in SAS Programming

Manipulate long data

amanda_cr — Mon, 05 Jun 2017 20:41:49 GMT

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.

Re: Manipulate long data

Astounding — Mon, 05 Jun 2017 20:53:40 GMT

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.

Re: Manipulate long data

kiranv_ — Mon, 05 Jun 2017 21:07:18 GMT

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;

Re: Manipulate long data

s_lassen — Tue, 06 Jun 2017 06:55:40 GMT

@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;

Re: Manipulate long data

amanda_cr — Tue, 06 Jun 2017 14:35:35 GMT

Thank you!

Re: Manipulate long data

amanda_cr — Tue, 06 Jun 2017 14:40:01 GMT

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