I have a list of people who have visited in certain years. I want to look at the years they didn't visit too up to year 15. How do I add those numbers in?
Have:
Study_ID Year
001 9
001 9
001 10
001 12
001 15
002 2
002 6
002 8
Want:
Study_ID Year
001 9
001 9
001 10
001 11
001 12
001 13
001 14
001 15
002 2
002 3
002 4
002 5
002 6
002 7
002 8
002 9
002 10
002 11
002 12
002 13
002 14
002 15
A lengthier solution to the one that is already posted but perhaps mine is more easy to understand😁.
data have; input Study_ID $ Year; cards; 001 9 001 9 001 10 001 12 001 15 002 2 002 6 002 8 ; run ; proc sql ; create table temp as select study_id, min(year) as minyear from have group by study_id order by study_id ; quit ; %let maxyear = 15 ; *set this value to what you desire ; data template ; set temp ; by study_id ; do year = minyear to &maxyear ; output ; end ; run ; data want ; merge have template ; by study_id year ; drop minyear ; run ;
Hi @A_Halps Assuming I understand your requirement-
data have;
input Study_ID $ Year;
cards;
001 9
001 9
001 10
001 12
001 15
002 2
002 6
002 8
;
data want;
_n_=.;
do until(last.study_id);
set have;
by study_id;
_year=year;
if _n_<year and _n_>. then
do _year=_n_+1 to year;
output;
end;
else output;
_n_=year;
end;
if _year<15 then do _year=_year to 15;
output;
end;
drop year;
rename _year=year;
run;
proc print noobs;run;
A lengthier solution to the one that is already posted but perhaps mine is more easy to understand😁.
data have; input Study_ID $ Year; cards; 001 9 001 9 001 10 001 12 001 15 002 2 002 6 002 8 ; run ; proc sql ; create table temp as select study_id, min(year) as minyear from have group by study_id order by study_id ; quit ; %let maxyear = 15 ; *set this value to what you desire ; data template ; set temp ; by study_id ; do year = minyear to &maxyear ; output ; end ; run ; data want ; merge have template ; by study_id year ; drop minyear ; run ;
data have; input Study_ID $ Year weight ; cards; 001 9 50 001 9 50 001 10 51 001 12 52 001 15 54 002 2 60 002 6 62 002 8 64 ; run ; proc sql ; create table temp as select study_id, min(year) as minyear from have group by study_id order by study_id ; quit ; %let maxyear = 15 ; *set this value to what you desire ; data template ; set temp ; by study_id ; do year = minyear to &maxyear ; output ; end ; run ; data want ; merge have template ; by study_id year ; drop minyear ; run ; data real_want ; update want (obs=0) want ; by study_id ; output ; run ;Obs Study_ID Year weight12345678
001 | 9 | 50 |
001 | 9 | 50 |
001 | 10 | 51 |
001 | 11 | 51 |
001 | 12 | 52 |
001 | 13 | 52 |
001 | 14 | 52 |
001 | 15 | 54 |
Hi @biopharma Thank you for your modesty and mention of those pleasing kind words. Very satisfying indeed as I recall an amazing encouragement/cheer from @Reeza (a selfless/amazing beautiful person) whose words were my 1st spark in this thread-https://communities.sas.com/t5/SAS-Programming/total-visits-and-average-score-in-a-datastep/td-p/411... Could never thank her enough and will always be indebted forever. And yes, we do disagree in our banter for sure i.e. Tea is unacceptable, coffee is better.
Okay, I would like some fun here too. lol. So extending my previous post with some hash fun 🙂
data have;
input Study_ID $ Year weight ;
cards;
001 9 50
001 9 51
001 10 51
001 12 52
001 15 54
002 2 60
002 6 62
002 8 64
;
run ;
%let maxyear = 15 ; *set this value to what you desire ;
data want;
if _n_=1 then do;
dcl hash H (dataset:'have(obs=0)',multidata:'y') ;
h.definekey ("year") ;
h.definedata (all:'y') ;
h.definedone () ;
end;
_n_=.;
do _iorc_=1 by 1 until(last.study_id);
set have;
by study_id;
h.add();
end;
do _iorc_=1 to h.num_items;
set have(keep=study_id year);
if _n_<year and _n_>. then
do year=_n_+1 to year-1;
output;
end;
if h.find()=0 then h.removedup();
output;
_n_=year;
end;
if year<15 then do year=year to &maxyear;
output;
end;
h.clear();
run;
Have a great weekend and stay safe.
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.