BookmarkSubscribeRSS Feed
sascodequestion
Fluorite | Level 6

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
;

7 REPLIES 7
Haikuo
Onyx | Level 15

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;
Astounding
PROC Star

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. 

sascodequestion
Fluorite | Level 6

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.

Astounding
PROC Star

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;

PGStats
Opal | Level 21

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;
PG
Ksharp
Super User

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;

Ksharp
Super User
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;

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
  • 7 replies
  • 1213 views
  • 0 likes
  • 5 in conversation