topic Need Help on below scenario in SAS Programming

Need Help on below scenario

nitink26 — Mon, 17 Jan 2022 07:42:17 GMT

Hi,

I need help on below query -

I have below dataset. From this data I need to capture Client, Current_Product, First Instance of Previous_product (If there are any - Highlighted in blue colour) and PREVIOUS_PRODUCT_year_month.

Data is sorted by client, descending Year_month

Data -

CLIENT	Current_Product	YEAR_MONTH
1	ABC	202111
1	ABC	202110
1	ABC	202109
1	ABC	202108
1	ABC	202107
2	CDE	202111
2	CDE	202110
2	CDE	202109
2	CDF	202108
2	CDE	202107

Output I am looking for -

CLIENT	Current_Product	PREVIOUS_PRODUCT	PREVIOUS_PRODUCT_year_month
1	ABC
2	CDE	CDF	202108

Thanks!

Re: Need Help on below scenario

andreas_lds — Mon, 17 Jan 2022 08:09:48 GMT

Is year_month formatted sas date or a string?

Posting data in usable form: a data step using datalines or cards, answers questions like mine automatically.

This step may work, hardly tested:

data want;
   set have;
   by Client;
   
   length 
      Previous_Product $ 3 Previous_Product_Year_Month 8
      _First_Product $ 3 _ignore 8
      ;
   retain _First_Product _ignore; 
   
   if first.Client then do;
      _First_Product = Current_Product;
      _ignore = 0;
   end;
   
   if Current_Product ^= _First_Product and not _ignore then do;
      Previous_Product = Current_Product;
      Previous_Product_Year_Month = Year_Month;
      Current_Product = _First_Product;
      output;
      _ignore = 1;
   end;
   
   if last.Client and not _ignore then do;
      Current_Product = _First_Product;
      output;
   end;
   
   drop Year_Month _:;
run;

Re: Need Help on below scenario

nitink26 — Mon, 17 Jan 2022 08:30:51 GMT

Hi,

Yes it's a string. Also, I have created a code to create a dataset -

data test1;
input client Current_Product $ Year_Month $;
datalines;
1 ABC 202111
1 ABC 202110
1 ABC 202109
1 ABC 202108
1 ABC 202107
2 CDE 202111
2 CDE 202110
2 CDE 202109
2 CDF 202108
2 CDE 202107
;
run;

Re: Need Help on below scenario

Reeza — Mon, 17 Jan 2022 16:44:54 GMT

Can they ever have more than 2 products? If so, how would that get shown? Or do you only care about the previous product alone?

Re: Need Help on below scenario

nitink26 — Mon, 17 Jan 2022 17:09:56 GMT

Hi,

Yes, that is possible. I just want to capture first change in the product and related Year_month, If there are more than one product changes.

I have created a new dataset and still I want the same output. Thanks!

data test1;
input client Current_Product $ Year_Month $;
datalines;
1 ABC 202111
1 ABC 202110
1 ABC 202109
1 ABC 202108
1 ABC 202107
2 CDE 202111
2 CDE 202110
2 CDE 202109
2 CDF 202108
2 CDE 202107

2 ABC 202106
;
run;

Re: Need Help on below scenario

Reeza — Mon, 17 Jan 2022 17:42:32 GMT

Similar solution to @andreas_lds

data test1;
	input client CURRENT_Product $ Year_Month $;
	datalines;
1 ABC 202111
1 ABC 202110
1 ABC 202109
1 ABC 202108
1 ABC 202107
2 CDE 202111
2 CDE 202110
2 CDE 202109
2 CDF 202108
2 CDE 202107
2 ABC 202106
;
run;

proc sort data=test1;
	by client descending year_Month;
run;

data want;
	set test1 (rename=current_product=product);
	by client product notsorted;
	retain current_product flag;

	if first.client then
		do;
			flag=1;
			current_product=product;
		end;

	if (first.product and flag=1 and not first.client) then
		do;
			flag=2;
			Prev_product=Product;
			Prev_Year_month=Year_month;
			output;
		end;
	else if last.client and flag=1 then
		output;
	drop flag year_month;
run;

@nitink26 wrote:

Hi,

Yes, that is possible. I just want to capture first change in the product and related Year_month, If there are more than one product changes.

I have created a new dataset and still I want the same output. Thanks!

data test1;
input client Current_Product $ Year_Month $;
datalines;
1 ABC 202111
1 ABC 202110
1 ABC 202109
1 ABC 202108
1 ABC 202107
2 CDE 202111
2 CDE 202110
2 CDE 202109
2 CDF 202108
2 CDE 202107

2 ABC 202106
;
run;