topic Re: Inheritence in SAS Programming

Inheritence

sascodequestion — Wed, 14 Sep 2016 15:06:10 GMT

Data Specifications:

I have three Different levels P1, P2 and P3 for each Product. The relation between them is P3 is a level under P2 and P2 is a level under P1. There will be only one row for P1, but there may multiple rows for P2 and P3. Also there will be a status Value (either A,B or C) always for Level-P3. And for P1 and P2 it will be empty.

So my requirement here is if All P3’s Have a status called “A” then I should get the same status for my P2. Now if all P2’s has the same status then my P1 also should get same status.

If all of my P3’s under a specific P2 are having 2 different statuses then I should get a 4th status called D for my P2, and if all of my P2 have two different status I should get status D for my P1.

data Have;
input P_Level$ Product$ PL1$ PL2$ B_Flag Status$;
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
;

data Want;
input P_Level$ Product$ PL1$ PL2$ B_Flag Status$;
infile cards missover;
cards;
P1 X . . . A
P2 X PL11 . . A
P3 X PL11 PL21 1 A
P3 X PL11 PL22 1 A
P3 X PL11 PL23 1 A
P2 X PL12 . . A
P3 X PL12 PL21 1 A
P3 X PL12 PL22 1 A
P3 X PL12 PL23 1 A
P2 X PL13 . . A
P3 X PL13 PL21 1 A
P3 X PL13 PL22 1 A
P3 X PL13 PL23 1 A
P1 Y . . . D
P2 Y PL11 . . D
P3 Y PL11 PL21 1 A
P3 Y PL11 PL22 1 B
P3 Y PL11 PL23 1 A
P2 Y PL12 . . A
P3 Y PL12 PL21 1 A
P3 Y PL12 PL22 1 A
P3 Y PL12 PL23 1 A
P2 Y PL13 . . D
P3 Y PL13 PL21 1 A
P3 Y PL13 PL22 1 A
P3 Y PL13 PL23 1 C
;

Re: Inheritence

Haikuo — Wed, 14 Sep 2016 16:09:57 GMT

The key is to 'group'.


data h1;
set have;
grp+(lag(p_level)='P3' and p_level ne 'P3')+0;
run;

data want;
 do until (last.grp);
  set h1;
    by grp;
	if status ne _sta and not missing(_sta) then _flag=1;
	_sta=status;
 end;

  do until (last.grp);
  set h1;
    by grp;
	status=ifc(p_level='P3',status, ifc(_flag=1,'D',_sta));
	output;
 end;
drop _:;
 run;

Re: Inheritence

Astounding — Wed, 14 Sep 2016 16:11:27 GMT

Questions to get started:

Does the real data set actually contain many P1 records, with a different product for each?
Does the variable PRODUCT actually have a nonmissing value on the P1 record? (If it doesn't, could it be added?)

I'm looking at an approach that would sort the records in reverse order ... making it easy to see all the P3 records for a given P2 record before having to assign a P2 status, for example. So in the worst case we could take this approach by adding the record number fo the data.

Re: Inheritence

sascodequestion — Wed, 14 Sep 2016 17:39:18 GMT

Does the real data set actually contain many P1 records, with a different product for each?

ANs : Yes

Does the variable PRODUCT actually have a nonmissing value on the P1 record? (If it doesn't, could it be added?)

ANs: For the P1 record , Product is the last non missing value

For the P2 record, Pl1 is the last non Missing value and for P3-Pl2.

Re: Inheritence

Astounding — Wed, 14 Sep 2016 18:48:05 GMT

Here's some untested code. It looks a little clumsy, but should be a viable approach.

data have2;

set have;

recno = _n_;

run;

proc sort data=have2;

by descending recno;

run;

data want;

set have2;

length P1_Status P2_Status $ 1;

retain P1_Status P2_Status;

if p_level = 'P3' then do;

if p2_status = ' ' then p2_status=status;

else if status ne p2_status then p2_status='D';

end;

else if p_level = 'P2' then do;

status = p2_status;

if p1_status = ' ' then p1_status = p2_status;

else if p2_status ne p1_status then p1_status = 'D';

p2_status = ' ';

end;

else if p_level = 'P1' then do;

status = p1_status;

p1_status = ' ';

end;

drop p1_status p2_status;

run;

proc sort data=want;

by recno;

run;

Re: Inheritence

PGStats — Thu, 15 Sep 2016 02:42:27 GMT

Here is an SQL based solution

proc sql;
create table P2 as
select "P2" as P_Level, 
    Product, 
    PL1, 
    case 
        when count(distinct Status) > 1 then "D"
        else max(Status)
        end as Status
from have
where p_level = "P3"
group by Product, PL1;
create table P1 as
select "P1" as P_Level, 
    Product,
    case 
        when count(distinct Status) > 1 then "D"
        else max(Status)
        end as Status
from P2
group by Product;
quit;

data want;
set have(where=(p_level="P3")) P1 P2;
run;

proc sort data=want; by product PL1 PL2; run;

proc print data=want; run;

Re: Inheritence

Ksharp — Thu, 15 Sep 2016 04:48:43 GMT


data Have;
input P_Level$ Product$ PL1$ PL2$ B_Flag Status$;
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 p1 p2 p3;
 by  Product PL1 p_level;
run;

Re: Inheritence

Ksharp — Thu, 15 Sep 2016 04:52:03 GMT

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;