Or make an index variable to keep the original order:
data Have;
input P_Level$ Product$ PL1$ PL2$ B_Flag Status$;
idx+1;
infile cards missover;
cards;
P1 X .
P2 X PL11 .
P3 X PL11 PL21 1 A
P3 X PL11 PL22 1 A
P3 X PL11 PL23 1 A
P2 X PL12 .
P3 X PL12 PL21 1 A
P3 X PL12 PL22 1 A
P3 X PL12 PL23 1 A
P2 X PL13 .
P3 X PL13 PL21 1 A
P3 X PL13 PL22 1 A
P3 X PL13 PL23 1 A
P1 Y .
P2 Y PL11 .
P3 Y PL11 PL21 1 A
P3 Y PL11 PL22 1 B
P3 Y PL11 PL23 1 A
P2 Y PL12 .
P3 Y PL12 PL21 1 A
P3 Y PL12 PL22 1 A
P3 Y PL12 PL23 1 A
P2 Y PL13 .
P3 Y PL13 PL21 1 A
P3 Y PL13 PL22 1 A
P3 Y PL13 PL23 1 C
;
run;
data p1 p2 p3;
set have;
if p_level='P1' then output p1;
else if p_level='P2' then output p2;
else if p_level='P3' then output p3;
run;
proc sql;
create table n_levels as
select product,PL1,count(distinct status) as n
from p3
group by product,PL1;
create table levels as
select distinct product,PL1,status
from p3;
quit;
data p2;
if _n_=1 then do;
if 0 then set n_levels;
declare hash ha_n(dataset:'n_levels');
ha_n.definekey('product','PL1');
ha_n.definedata('n');
ha_n.definedone();
if 0 then set levels;
declare hash ha_le(dataset:'levels');
ha_le.definekey('product','PL1');
ha_le.definedata('status');
ha_le.definedone();
end;
set p2;
ha_n.find();
if n ne 1 then status='D';
else ha_le.find();
drop n;
run;
/*****************/
proc sql;
create table n_levels as
select product,count(distinct status) as n
from p2
group by product;
create table levels as
select distinct product,status
from p2;
quit;
data p1;
if _n_=1 then do;
if 0 then set n_levels;
declare hash ha_n(dataset:'n_levels');
ha_n.definekey('product');
ha_n.definedata('n');
ha_n.definedone();
if 0 then set levels;
declare hash ha_le(dataset:'levels');
ha_le.definekey('product');
ha_le.definedata('status');
ha_le.definedone();
end;
set p1;
ha_n.find();
if n ne 1 then status='D';
else ha_le.find();
drop n;
run;
data want;
set p3 p2 p1;
run;
proc sort data=want;by idx;run;
... View more
