Creating (Expanding) the data points that are missing and giving them value 0

[Loes]

Hello,

I'm trying to expand the data that I have for the weeks that are missing. I have weekly data and the number of purchases in a certain week. Now I want to create an observation for the weeks that are missing as the number of purchases in those weeks are 0. The data looks like the following (except I have way less missing weeks):

Year    Week Num_purch

2008    1    10
2008    3    4
2008    10   5
2008    21   10
2008    22   3 
...
2016    51   10 

What I want is then:

Year    Week    Num_purch

2008    1       10
2008    2       0
2008    3       4
2008    4       0
2008    5       0
2008    6       0
etc.

Also, my data contains several years from 2008 to 2016.

I have no clue where to start. I think with a proc expand. Also don't know how to google on this problem, do not seem to find the same problem.

Oh, forgot to mention, I have this not one time, but for 200 different ID codes in one time.

Thanks!

[RW9]

Lag an retain.  For example, and this is just pseudocode as you have not provided test data in the form of a datastep:

data want;
  set have;
  if lag(year)=year and lag(week) ne week then do;
    do new_week=lag(week) to week;
      output;
    end;
  end;
run;

[Astounding]

While there are several hard ways to make this happen, an easy way is to create your own shell with all the weeks and years.  For example:

data shell;

num_purch=0;

do year = 2008 to 2016;

   do week = 1 to 52;

      output;

   end;

end;

run;

Then you can merge with the data that you have.  Mention SHELL first, so values that you have overwrite the zeros:

data want;

merge shell have;

by year week;

run;

**************** EDITED:

GIven that you have many ID codes, I will assume the data set you have is sorted by ID:

data shell;

set have (keep=ID);

by ID;

if last.ID;

num_purch=0;

do year = 2008 to 2016;

   do week = 1 to 52;

      output;

   end;

end;

run;

Then you can merge with the data that you have.  Mention SHELL first, so values that you have overwrite the zeros:

data want;

merge shell have;

by ID year week;

run;

Perhaps another way will turn out to be simpler, but this is at least a reasonable, clear way to do it.

[Loes]

This would probably work when I have to do it only one time. But (my fault forgot to mention) I have the same stituation for many many different ID codes. I try to make it work on 200 different ID codes, but later it has to be even more upscaled.

[Loes]

Thank you! This works for now. Although I deleted the num_purch=0, and after merging changed the missing values to 0. That seems to work better in my case 😉 .

[Ksharp]

data have;
input Year    Week Num_purch;
cards;
2008    1    10
2008    3    4
2008    10   5
2008    21   10
2008    22   3 
;
run;
data want;
 merge have have(keep=year week rename=(year=_year week=_week) firstobs=2);
 output;
 if year=_year then do;
  do i=week+1 to _week-1;
   week=i;Num_purch=0;output;
  end;
 end;
 drop _: i;
run;