Hello,
I need to determine the MIN date for a subproduct associated with a product. The subproduct ID can change to another and than return to the original. I need the MIN date for the active sequence of the subproduct .
An example in the table below - in green the date that I want for each product.
I thought doing this by creating an ID column with a counter and then select the min date for each product when ID = 1.
The problem is that I can't create the ID column the way I need.
Any help on how to create the ID?
Thanks,
Jorge
How do you know what SubProduct you want the Min_Date to be associated with?
and what if the there are 2 obs with the same min_date?
Hello,
For each Product I want the Min date for the SubProduct that is "active" at the moment of our analysis since it changed to the current Subproduct.
For example, product a had subproduct x, than changed for subproduct y and than returned to subproduct x. Since the last time it has returned to subproduct x, it had several dates (01-02-2022; 05/02/2022; 10/02/2022), I want the Min date since it has returned to subproduct x (01-02-2022).
2 observations with the same min date doesn't happen in this case.
Thanks,
Jorge
How about
data have;
input product $ sub_product $ date :ddmmyy10.;
format date ddmmyy10.;
datalines;
a x 10/02/2022
a x 05/02/2022
a x 01/02/2022
a y 28/01/2022
a x 20/02/2022
b x 01/02/2022
b x 28/01/2022
b y 10/01/2022
b y 01/01/2022
d x 27/01/2022
d y 20/01/2022
d x 18/01/2022
d x 16/01/2022
d y 10/01/2022
;
data want(drop = d);
d = '31dec9999'd;
do until (last.product);
set have;
by product;
if sub_product = 'x' and date < d then d = date;
end;
do until (last.product);
set have;
by product;
flag = date = d;
output;
end;
run;
Result:
product sub_product date flag a x 10/02/2022 0 a x 05/02/2022 0 a x 01/02/2022 1 a y 28/01/2022 0 a x 20/02/2022 0 b x 01/02/2022 0 b x 28/01/2022 1 b y 10/01/2022 0 b y 01/01/2022 0 d x 27/01/2022 0 d y 20/01/2022 0 d x 18/01/2022 0 d x 16/01/2022 1 d y 10/01/2022 0
Hi,
I'm getting different results:
Also, the "active" subproduct is not always x, could be another.
Thanks.
Found a solution to this
PROC SQL;
CREATE TABLE WORK.MAX_DATE AS
SELECT product,
subproduct,
MAX(date) FORMAT = DDMMYY10. AS date
FROM WORK.data
GROUP BY 1, 2
ORDER BY 1, 3
;
QUIT;
PROC SORT DATA=WORK.MAX_DATE;
BY product descending date;
RUN;
data ID;
set WORK.MAX_DATE;
by product NOTSORTED;
retain id;
if first.product then id = 1;
else id+1;
run;
/* Select MIN Date since last subproduct change */
PROC SQL;
CREATE TABLE WORK.MIN_DATE AS
SELECT a.product,
a.subproduct,
MIN(a.date) FORMAT = DDMMYY10. as date
FROM WORK.data
INNER JOIN (SELECT * FROM ID WHERE ID = 2) b ON a.product = b.product AND a.date > b.date
GROUP BY 1, 2
ORDER BY 1, 3
;
QUIT;
Thanks.
Jorge
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.