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!
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 2021072 ABC 202106
;
run;
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;
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;
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;
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 2021072 ABC 202106
;
run;
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.