if one of these statements is true-PLEASE HELP ME

[Dhana18]

Good morning,

I have a data set set similar to this.  

DATA TEST;
INPUT EVENT_ID TEST$ RESULTS $;
DATALINES;
1234 NAATR N
1234 NAATG P
1234 NAATP N
1234 NAATU N
1256 NAATU N
1256 NAATP N
1256 NAATR N
1256 NAATG N
3456 NAATR P
3456 NAATG P
3456 NAATP P
;
RUN;

I sorted and transposed the  data set

EVENT_ID NAATR NAATG NAATP NAATU

1234             N            P         N            N

1256             N            N        N            N

3456             P           P         P

i WOULD LIKE TO HAVE THIS (BELOW)

EVENT_ID ANY_NAAT

1234             P

1256            N

3456           P

[Kurt_Bremser]

Can be done directly from the initial dataset with by-processing and retain:

data have;
input event_id test $ results $;
datalines;
1234 NAATR N
1234 NAATG P
1234 NAATP N
1234 NAATU N
1256 NAATU N
1256 NAATP N
1256 NAATR N
1256 NAATG N
3456 NAATR P
3456 NAATG P
3456 NAATP P
;
run;

data want (keep=event_id any_naat);
set have;
by event_id;
retain any_naat;
if first.event_id then any_naat = 'N';
if results = 'P' then any_naat = 'P';
if last.event_id then output;
run;

proc print data=want noobs;
run;

Result:

event_id    any_naat

  1234         P    
  1256         N    
  3456         P    

[RW9]

Its very hard to read code when it is SHOUTED at me.  Perhaps something like:

data want;
  set have;
  any_naat=ifc(whichc("P",naatr,naatg,naatp,naatu),"P","N");
run;

Based on your transposed dataset.  However you could do the same thing in proc sql without transposing:

proc sql;
  create table want as
  select  distinct event_id,
          case when exists(select distinct event_id from test where results="P") then "P" 
               else "N" end as any_naat
  from    test;
quit;

[Dhana18]

Sorry, I was not shouting at you. When I transposed the dataset, I no longer have results variable. It looks like this
Event_id naatr naatg naatp naatu
1234 n p n n
1256 n n n n
3456 p p p

Since I have no results variable, I could not use results="p"

proc sql;

create table want as

select distinct event_id,

case when exists(select distinct event_id from test where results="P") then "P"
else "N" end as any_naat

from test;

quit;

what should I use instead? Your help will be appreciated.

[Kurt_Bremser]

The whole transposition business is unnecessary. See my code, and @RW9's SQL. Both work from the original dataset.

[RW9]

The first code I presented would work on your transposed data, the proc sql works on your original data without need for transposing.  Please post test data in the form of a datastep in future:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Jagadishkatam]

please try

data want;
set have;
by event_id;
if results='N' then ord=0;
else if results='P' then ord=1;
run;


proc sort data=want;
by event_id ord;
run;

data want2;
set want;
by event_id ord;
if last.event_id;
run;

[Ksharp]

data have;
input event_id test $ results $;
datalines;
1234 NAATR N
1234 NAATG P
1234 NAATP N
1234 NAATU N
1256 NAATU N
1256 NAATP N
1256 NAATR N
1256 NAATG N
3456 NAATR P
3456 NAATG P
3456 NAATP P
;
run;
proc sql;
select EVENT_ID ,max(results) as ANY_NAAT
 from have
  group by EVENT_ID;
quit;