topic Re: if one of these statements is true-PLEASE HELP ME in SAS Programming

if one of these statements is true-PLEASE HELP ME

Dhana18 — Mon, 20 Aug 2018 14:36:43 GMT

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

Re: if one of these statements is true-PLEASE HELP ME

Kurt_Bremser — Mon, 20 Aug 2018 14:44:20 GMT

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

Re: if one of these statements is true-PLEASE HELP ME

RW9 — Mon, 20 Aug 2018 14:44:52 GMT

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;

Re: if one of these statements is true-PLEASE HELP ME

Dhana18 — Mon, 20 Aug 2018 16:36:22 GMT

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.

Re: if one of these statements is true-PLEASE HELP ME

Kurt_Bremser — Mon, 20 Aug 2018 17:00:13 GMT

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

Re: if one of these statements is true-PLEASE HELP ME

RW9 — Tue, 21 Aug 2018 07:48:30 GMT

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-data-AKA-generate/ta-p/258712

Re: if one of these statements is true-PLEASE HELP ME

Jagadishkatam — Tue, 21 Aug 2018 09:49:12 GMT

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;

Re: if one of these statements is true-PLEASE HELP ME

Ksharp — Tue, 21 Aug 2018 13:34:57 GMT

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;