topic Re: How to find if variable value changed and its type of change in SAS Programming

How to find if variable value changed and its type of change

Ashwani_Gupta — Sun, 18 Oct 2020 08:20:08 GMT

Hi everyone,

hope you are well and doing good.

i have one data set like below.

date	Product
jan	a
feb	a
mar	a
apr	b
may	b
june	c
jul	c
aug	c
sep	c
ovt	b
nov	b
dec	b

and output for this data step would be like below. Basically i want to check if product was change temporary or permanent.

from	to	date_start	date_end	change
a	b	jan	mar	temporary
b	c	apr	may	temporary
c	b	jul	sep	permanent

Please suggest on this how to do this. ?

Re: How to find if variable value changed and its type of change

Ksharp — Sun, 18 Oct 2020 11:09:24 GMT

Assuming I understood what you mean.

data have;
input date $	Product $;
cards;
jan	a
feb	a
mar	a
apr	b
may	b
june	c
jul	c
aug	c
sep	c
ovt	b
nov	b
dec	b
;
data temp;
do until(last.product);
 set have;
 by product notsorted;
 if first.product then date_start=date;
end;
date_end=date;
keep product date_start date_end;
run;
data want;
 merge temp temp(firstobs=2 keep=product rename=(product=p));
 from=product;to=p;
 if _n_=1 then do;
  declare hash h();
  h.definekey('p');
  h.definedone();

  declare hash hh();
  hh.definekey('product');
  hh.definedone();
 end;

 if h.check() ne 0 then do;change='temporary ';h.add();end;
  else change='permanent';

 if hh.check() ne 0 then do; hh.add();output;end;
drop product p;
run;

Re: How to find if variable value changed and its type of change

Ashwani_Gupta — Sun, 18 Oct 2020 12:07:09 GMT

Hi Ksharp,
Thanks for your response ,

This code handle the case right, but if there is change in data like below it will not work,

date	Product
jan	a
feb	a
mar	a
apr	b
may	b
june	b
jul	b
aug	b
sep	b
ovt	b
nov	b
dec	b

then it should give only output like below.

from	to	date_start	date_end	change
a	b	jan	mar	permanent

with your code we will not achive this.

Re: How to find if variable value changed and its type of change

Kurt_Bremser — Sun, 18 Oct 2020 12:14:20 GMT

Please define, in plain language, what constitues a permanent or temporary change.

Re: How to find if variable value changed and its type of change

FreelanceReinh — Sun, 18 Oct 2020 12:41:47 GMT

Hi @Ashwani_Gupta and welcome to the SAS Support Communities!

Try this:

/* Create test data */

data have;
date='01JAN19'd;
do _n_=1 to 12;
  product=byte(97+ranpoi(990067878,1));
  output;
  date=intnx('month',date,1);
end;
format date monname3.;
run;

/* Determine product changes */

data want(drop=_: date product);
do until(last1);
  set have end=last1;
  by product notsorted;
  if first.product then _lfdate=date;
end;
do until(last2);
  set have end=last2 curobs=_n_;
  by product notsorted;
  retain _f 1 from to date_start date_end;
  if _n_=1 then do;
    from=product;
    date_start=date;
  end;
  else if first.product then do;
    to=product;
    length change $9;
    change=ifc(date=_lfdate,'permanent','temporary');
    output;
    _f=0;
    from=to;
    date_start=date;
  end;
  if last.product then date_end=date;
end;
if _f then do;
  to=from;
  change='none';
  output;
end;
format date_: monname3.;
run;

Also try different input data, e.g., where no change occurs or frequent changes like this:

data have;
input date product $;
format date monname3.;
cards;
1  a
40  b
80  a
120 b
150 a
;

(Edit: Changed last date value in second test dataset from 160 to 150 to avoid a gap. -- The suggested solution assumes consecutive months.)

Re: How to find if variable value changed and its type of change

Ksharp — Mon, 19 Oct 2020 12:56:07 GMT

So how do you define 'permanent' ? If it is the second last product ,it should be permanent ,otherwise 'temporary' ?

data have;
input date $	Product $;
cards;
jan	a
feb	a
mar	a
apr	b
may	b
june	b
jul	b
aug	b
sep	b
ovt	b
nov	b
dec	b
;
data temp;
do until(last.product);
 set have;
 by product notsorted;
 if first.product then date_start=date;
end;
date_end=date;
keep product date_start date_end ;
run;
data temp; 
 set temp nobs=nobs;
 n=nobs;
run;
data want;
 merge temp temp(firstobs=2 keep=product rename=(product=p));
 from=product;to=p;

 if _n_=n-1  then change='permanent';
  else  change='temporary ';

 if _n_ ne n then output;
drop product p n;
run;