BookmarkSubscribeRSS Feed
jorgemaiden
Fluorite | Level 6

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.

 

jorgemaiden_0-1644583919804.png

 

Any help on how to create the ID?

 

Thanks,

Jorge

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6

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
Tourmaline | Level 20

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
Fluorite | Level 6

Hi,

 

I'm getting different results:

jorgemaiden_0-1644586615704.png

 

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

 

Thanks.

jorgemaiden
Fluorite | Level 6

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1622 views
  • 1 like
  • 2 in conversation