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

Reply
New Contributor
Posts: 3

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

[ Edited ]

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!

Super User
Super User
Posts: 8,987

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

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;
Super User
Posts: 6,378

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

[ Edited ]

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.

New Contributor
Posts: 3

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

[ Edited ]
Posted in reply to Astounding

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.

 

 

New Contributor
Posts: 3

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

Posted in reply to Astounding
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 Smiley Wink .
Super User
Posts: 10,520

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

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;
Ask a Question
Discussion stats
  • 5 replies
  • 79 views
  • 0 likes
  • 4 in conversation