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!
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;
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.
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.
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.