Creating observations for missing values

[Shmuel]

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).

[novinosrin]

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;

[Ksharp]

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;

[novinosrin]

@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

[Reeza]

@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.

---