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

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

Loes — Thu, 08 Feb 2018 12:00:46 GMT

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!

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

RW9 — Thu, 08 Feb 2018 11:55:48 GMT

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;

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

Astounding — Thu, 08 Feb 2018 12:06:51 GMT

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;

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;

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.

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

Loes — Thu, 08 Feb 2018 12:05:26 GMT

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.

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

Ksharp — Thu, 08 Feb 2018 12:10:29 GMT

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;

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

Loes — Thu, 08 Feb 2018 12:25:48 GMT

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