BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rkolupoti9001
Calcite | Level 5
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Ksharp
Super User

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

rkolupoti9001
Calcite | Level 5

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

Astounding
PROC Star

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.

rkolupoti9001
Calcite | Level 5

Hi, It worked!!!Smiley Happy

Thank you!!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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