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.
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;
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;
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.
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;
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.