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.
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.
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
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
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.
Hi, It worked!!!
Thank you!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.