Use @Reeza's and @Astounding's logic but create the all_combinations with the next code
proc sql;
create table temp1 as
select distinct id, date_of_entry;
quit;
data all_combinations;
set temp1;
start_year = year(date_of_entry) - 1;
/* change if needed accoring to date_of_entry format */
do year = start_year to (start_year +2);
do qtr=1 to 4;
output;
end; end;
keep id year qtr date_of_entry; /* or drop start_year */
run;
continue with code of @Astounding (sort, merge, replace missing spent by 0).
I'm afraid I haven't read through the thread as I am feeling very lazy. However, for your sample HAVE and WANT, and here you go:
data have;
input ID Year Qtr Spent;
datalines;
1 2010 1 50
1 2010 2 100
1 2010 4 25
1 2011 2 100
1 2011 4 200
;
data want;
do until(last.id);
do _n_=1 by 1 until(last.year);
set have;
by id year;
if first.year then _qtr=qtr;
if _n_=1 and qtr ne _n_ then
do;
_qtr=qtr;
_spent=spent;
do qtr=_n_ to qtr-1;
spent=0;
output;
end;
spent=_spent;
output;
_qtr=qtr;
end;
else if _qtr ne qtr then
do;
_spent=spent;
do qtr=_qtr+1 to qtr-1;
spent=0;
output;
end;
spent=_spent;
output;
_qtr=qtr;
end;
else output;
end;
end;
drop _:;
run;
It is very easy for SQL.
data have;
input ID Year Qtr Spent ;
cards;
1 2010 1 50
1 2010 2 100
1 2010 4 25
1 2011 2 100
1 2011 4 200
;
run;
data qtr;
do qtr=1 to 4;
output;
end;
run;
proc sql;
create table want as
select a.*,coalesce(spent,0) as spent
from
(
select * from
(select distinct id from have),
(select distinct year from have),
(select distinct qtr from qtr)
) as a
left join have as b
on a.id=b.id and a.year=b.year and a.qtr=b.qtr ;
quit;
@Ksharp Thank you. It's something very intriguing to read your brilliant innovative solutions. I wish I had the drive to be keen, driven and dedicated as you are however I am too lazy to venture into much learning and applying. My bad. Thank you for making me think. Cheers!
Regards,
Naveen Srinivasan
@novinosrin your participation on here would indicate the opposite 😉
@novinosrin wrote:
I wish I had the drive to be keen, driven and dedicated as you are however I am too lazy to venture into much learning and applying. My bad. Thank you for making me think.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.