topic find the number of recurring items in year+1 in New SAS User

find the number of recurring items in year+1

EmadNoureldeen — Sun, 27 Dec 2020 23:54:39 GMT

Hello everyone, I have these data and I want to find the item that recurring next year

the description of what I need : I want to create a dummy variable for the item, if the item in year+1 is in the year put 0, if it is not put 1. in summary, 1 for nonrecurring and 0 for recurring.

The data inputs

Code Year item

000001	2017	AB
000001	2017	AD
000001	2017	BC
000001	2018	AD
000001	2018	CC
000001	2019	CC
000001	2019	BC
000002	2016	FF
000002	2016	GG
000002	2016	DD
000002	2017	FF
000002	2017	GG
000002	2017	HH
000002	2018	HH
000002	2018	BB
000002	2018	GG
000002	2019	BB
000002	2019	GG
000002	2019	FF

The output that I need

Code Year item recurring

000001	2017	AB	1
000001	2017	AD	1
000001	2017	BC	1
000001	2018	AD	0
000001	2018	CC	1
000001	2019	CC	0
000001	2019	BC	1
000002	2016	FF	1
000002	2016	GG	1
000002	2016	DD	1
000002	2017	FF	0
000002	2017	GG	0
000002	2017	HH	1
000002	2018	HH	0
000002	2018	BB	1
000002	2018	GG	0
000002	2019	BB	0
000002	2019	GG	0
000002	2019	FF	1

Thanks in advance.

Re: find the number of recurring items in year+1

novinosrin — Mon, 28 Dec 2020 01:04:16 GMT

data have;
input Code  $     Year     item $;
cards;
000001	2017	AB
000001	2017	AD
000001	2017	BC
000001	2018	AD
000001	2018	CC
000001	2019	CC
000001	2019	BC
000002	2016	FF
000002	2016	GG
000002	2016	DD
000002	2017	FF
000002	2017	GG
000002	2017	HH
000002	2018	HH
000002	2018	BB
000002	2018	GG
000002	2019	BB
000002	2019	GG
000002	2019	FF
;

proc sql;
 create table need as
 select a.*	,min(a.year)=a.year	or a.item ne b.item as recur
 from have a left join have b
 on a.code=b.code and a.year-1=b.year
 and a.item=b.item
 group by a.code
 order by  a.code,a.year;
quit;

Re: find the number of recurring items in year+1

novinosrin — Mon, 28 Dec 2020 11:44:40 GMT



data have;
input Code  $     Year     item $;
cards;
000001	2017	AB
000001	2017	AD
000001	2017	BC
000001	2018	AD
000001	2018	CC
000001	2019	CC
000001	2019	BC
000002	2016	FF
000002	2016	GG
000002	2016	DD
000002	2017	FF
000002	2017	GG
000002	2017	HH
000002	2018	HH
000002	2018	BB
000002	2018	GG
000002	2019	BB
000002	2019	GG
000002	2019	FF
;

data want ;
 if _n_ = 1 then do ;
  dcl hash h() ;
  h.definekey ("item","year") ;
  h.definedone () ;
 end ; 
 do  until(last.code);
  do until(last.year);
   set have;
   by code year;
   h.add();
   _iorc_=h.check(key:item,key:year-1) ;
   recur=  _iorc_ ne 0;
   output;
  end;
  _iorc_=h.remove(key:item,key:year-1);
 end;
 h.clear();
run;

Re: find the number of recurring items in year+1

EmadNoureldeen — Mon, 28 Dec 2020 19:52:33 GMT

Re: find the number of recurring items in year+1

FreelanceReinh — Mon, 28 Dec 2020 08:34:54 GMT

Hello @EmadNoureldeen,

Glad to see that @novinosrin's solution worked for you. Then it would be fair and help later readers if you marked his helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: Select the post containing the working code as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.

Re: find the number of recurring items in year+1

Ksharp — Mon, 28 Dec 2020 10:59:07 GMT

data have;
input Code  $     Year     item $;
cards;
000001	2017	AB
000001	2017	AD
000001	2017	BC
000001	2018	AD
000001	2018	CC
000001	2019	CC
000001	2019	BC
000002	2016	FF
000002	2016	GG
000002	2016	DD
000002	2017	FF
000002	2017	GG
000002	2017	HH
000002	2018	HH
000002	2018	BB
000002	2018	GG
000002	2019	BB
000002	2019	GG
000002	2019	FF
;
data want;
 if _n_=1 then do;
   declare hash h(dataset:'have',hashexp:20);
   h.definekey(all:'y');
   h.definedone();
 end;
set have;
recurring=(h.check(key:code,key:year-1,key:item) ne 0);
run;

Re: find the number of recurring items in year+1

EmadNoureldeen — Mon, 28 Dec 2020 19:55:05 GMT

Ok, I did it