Condition for the logic

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Condition for the logic

Order IDOrder detail idStatus
AAA111Confirmed
AAA234Confirmed
AAA345Confirmed
BBB111Cancelled
BBB234Cancelled
BBB345Cancelled
CCC111Confirmed
CCC234Cancelled
CCC345Confirmed

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 1Header 2Header 3Header 4
AAA111ConfirmedConfirmed
AAA234Confirmed
AAA345Confirmed
BBB111CancelledCancelled
BBB234Cancelled
BBB345Cancelled
CCC111ConfirmedCancelled Line
CCC234Cancelled
CCC345Confirmed

Thanks in Advance.


Accepted Solutions
Solution
‎11-17-2014 11:27 AM
Super User
Posts: 5,498

Re: Condition for the logic

Posted in reply to rkolupoti9001

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.

View solution in original post


All Replies
Super User
Posts: 10,020

Re: Condition for the logic

Posted in reply to rkolupoti9001

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

Contributor
Posts: 21

Re: Condition for the logic

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

Solution
‎11-17-2014 11:27 AM
Super User
Posts: 5,498

Re: Condition for the logic

Posted in reply to rkolupoti9001

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.

Contributor
Posts: 21

Re: Condition for the logic

Posted in reply to Astounding

Hi, It worked!!!Smiley Happy

Thank you!!!!

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 222 views
  • 5 likes
  • 3 in conversation