Flagging Data from multiple lines

[IgawaKei29]

Good Morning Everyone!

I have an issue that I have been trying to wrap my noggin around this morning.  

I have the following data lines:

Product Number	In Stock?
2408200	NO
2408200	YES
2529100	NO
9252992	NO
9252992	YES
2295299	YES
2929592	NO

So in my example I want to flag and retain 2408200 and 9252992 since the item was out of stock and then came back in stock.  

My case statement didn't do what I wanted it to do:

CASE
when Stock in ('NO') and Stock in ('YES') then 'FLAG'
else 'NOPE'
end as Stock_Status

So I am curious the best course of action to tackle this issue.

Thank you!

[PeterClemmensen]

So what does your desired result look like from this sample data?

[IgawaKei29]

Hello @PeterClemmensen I was thinking about it looking something like this:

Product Number	In Stock?	Flagged
2408200	NO/YES	YES
9252992	NO/YES	YES

So creating a Flagged field and then a combined In Stock.  So the full data would look something like this:

Product Number	In Stock?	Flagged
2408200	NO/YES	YES
9252992	NO/YES	YES
2529100	NO
2295299	YES
2929592	NO

[ed_sas_member]

Hi @IgawaKei29 

Here is an approach to achieve this:

data have;
	input Product_Number In_Stock $;
	datalines;
2408200 NO
2408200 YES
2529100 NO
9252992 NO
9252992 YES
2295299 YES
2929592 NO
;
run;

proc sort data=have out=have_sorted;
	by Product_Number;
run;

proc transpose data=have_sorted out=have_tr (drop=_:) prefix=in__;
	var in_stock;
	by Product_Number ;
run;

data want;
	set have_tr;
	in_stock = catx("/", of in__:);
	if find(in_stock, 'NO/YES')>0  then flagged = "YES";
	drop in__:;
run;

NB: if the product has been out of stock, then in stock, and then out of stock, do you want to flag it?

[PeterClemmensen]

Ok. And what if the product, once again, went out of stock?

[IgawaKei29]

@PeterClemmensen and @ed_sas_member 

I don't need to see that for this particular issue I am working on.  However it would be interesting to see if you know of a way to show that?

Thanks @ed_sas_member I am going to try out your code and see what happens.

[ed_sas_member · Accepted solution]

Hi @IgawaKei29 

The second option would manage this particular cases:

proc sort data=have out=have_sorted;
	by Product_Number;
run;

proc transpose data=have_sorted out=have_tr (drop=_:) prefix=in__;
	var in_stock;
	by Product_Number ;
run;

/*option 1*/
data want;
	set have_tr;
	in_stock = catx("/", of in__:);
	if find(in_stock, 'NO/YES')>0  then flagged = "YES";
	drop in__:;
run;

/*option 2*/
data want;
	set have_tr;
	in_stock = catx("/", of in__:);
	if prxmatch('/NO\/(YES)+\s*$/',in_stock)>0  then flagged = "YES";
	drop in__:;
run;

[novinosrin]

Hi @IgawaKei29  For what it's worth, a SQL solution

data have;
	input Product_Number In_Stock $;
	datalines;
2408200 NO
2408200 YES
2529100 NO
9252992 NO
9252992 YES
2295299 YES
2929592 NO
;
run;

proc sql;
create table want as
select distinct Product_Number,ifc(count(distinct In_Stock)>1,'NO/YES',In_Stock) as In_Stock,
ifc(calculated In_Stock='NO/YES','YES',' ') as Flagged
from have
group by Product_Number;
quit;

[IgawaKei29]

Thank you @novinosrin and @ed_sas_member 

Both solutions worked great and I appreciate seeing both the data step and the sql versions of the solution.  

Thanks again!