Create ID by group

[jorgemaiden]

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

[PeterClemmensen]

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?

[jorgemaiden]

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

[PeterClemmensen]

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 

[jorgemaiden]

Hi,

I'm getting different results:

Also, the "active" subproduct is not always x, could be another.

Thanks.

[jorgemaiden]

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