DATA Step, Macro, Functions and more

Manipulate long data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Manipulate long data

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

 


Accepted Solutions
Solution
‎06-06-2017 10:33 AM
PROC Star
Posts: 102

Re: Manipulate long data

Posted in reply to amanda_cr

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

View solution in original post


All Replies
Super User
Posts: 5,505

Re: Manipulate long data

Posted in reply to amanda_cr

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.

PROC Star
Posts: 325

Re: Manipulate long data

[ Edited ]
Posted in reply to amanda_cr

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;
Occasional Contributor
Posts: 13

Re: Manipulate long data

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

Solution
‎06-06-2017 10:33 AM
PROC Star
Posts: 102

Re: Manipulate long data

Posted in reply to amanda_cr

@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;
Occasional Contributor
Posts: 13

Re: Manipulate long data

Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 174 views
  • 0 likes
  • 4 in conversation