topic Re: Condition for the logic in SAS Programming

Condition for the logic

rkolupoti9001 — Fri, 14 Nov 2014 19:51:18 GMT

Order ID	Order detail id	Status
AAA	111	Confirmed
AAA	234	Confirmed
AAA	345	Confirmed
BBB	111	Cancelled
BBB	234	Cancelled
BBB	345	Cancelled
CCC	111	Confirmed
CCC	234	Cancelled
CCC	345	Confirmed

Data is in this format.

I need to keep a logic to display this in the 4th column

Need output for only lines with 111 in Header2.

For CCC its calcelled line because one of the detail i.e. 234 has cancelled line.

Header 1	Header 2	Header 3	Header 4
AAA	111	Confirmed	Confirmed
AAA	234	Confirmed
AAA	345	Confirmed
BBB	111	Cancelled	Cancelled
BBB	234	Cancelled
BBB	345	Cancelled
CCC	111	Confirmed	Cancelled Line
CCC	234	Cancelled
CCC	345	Confirmed

Thanks in Advance.

Re: Condition for the logic

Ksharp — Sat, 15 Nov 2014 08:48:15 GMT

If I understood what you mean.

data have;
input id $ detail $ status : $20.;
cards;
AAA     111     Confirmed
AAA     234     Confirmed
AAA     345     Confirmed
BBB     111     Cancelled
BBB     234     Cancelled
BBB     345     Cancelled
CCC     111     Confirmed
CCC     234     Cancelled
CCC     345     Confirmed
;
run;
proc sql;
create table want as
 select *,case when sum(status='Cancelled')     gt 0 then 'Cancelled' else 'Confirmed' end as flag 
  from have
   group by id;
quit;

Xia Keshan

Re: Condition for the logic

rkolupoti9001 — Mon, 17 Nov 2014 15:48:58 GMT

Hi Xia,

Thanks for replying. So what I want is for Id CCC there is both Cancelled and Confirmed status, for them I want "Cancelled line".

Thanks in Advance

Re: Condition for the logic

Astounding — Mon, 17 Nov 2014 16:27:37 GMT

One type of approach:

data want;

ever_cancelled='N';

length header_4 $ 14;

do until (last.order_id);

set have;

by order_id;

if status='Cancelled' then ever_cancelled='Y';

end;

do until (last.order_id);

set have;

by order_id;

if first.order_id then header_4=Status;

else header_4=' ';

if header_4='Confirmed' and ever_cancelled='Y' then header_4='Cancelled Line';

output;

end;

drop ever_cancelled;

run;

The top loop determines if there was ever a cancellation. The bottom loop reads the same records, adjusts the new column accordingly, and outputs ever record.

Good luck.

Re: Condition for the logic

rkolupoti9001 — Mon, 17 Nov 2014 17:24:22 GMT

Hi, It worked!!!

Thank you!!!!