topic Re: Flagging Data from multiple lines in SAS Programming

Flagging Data from multiple lines

IgawaKei29 — Tue, 10 Mar 2020 13:03:08 GMT

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!

Re: Flagging Data from multiple lines

PeterClemmensen — Tue, 10 Mar 2020 13:04:44 GMT

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

Re: Flagging Data from multiple lines

IgawaKei29 — Tue, 10 Mar 2020 13:09:51 GMT

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

Re: Flagging Data from multiple lines

ed_sas_member — Tue, 10 Mar 2020 13:20:21 GMT

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?

Re: Flagging Data from multiple lines

PeterClemmensen — Tue, 10 Mar 2020 13:21:40 GMT

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

Re: Flagging Data from multiple lines

IgawaKei29 — Tue, 10 Mar 2020 13:24:14 GMT

@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.

Re: Flagging Data from multiple lines

ed_sas_member — Tue, 10 Mar 2020 13:28:51 GMT

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;

Re: Flagging Data from multiple lines

novinosrin — Tue, 10 Mar 2020 13:29:40 GMT

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;

Re: Flagging Data from multiple lines

IgawaKei29 — Tue, 10 Mar 2020 13:37:24 GMT

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!