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!
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;
So what does your desired result look like from this sample data?
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 |
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?
Ok. And what if the product, once again, went out of stock?
@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.
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;
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.