Forecasting using SAS Forecast Server, SAS/ETS, and more

How to add all missing observations into a data set?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to add all missing observations into a data set?

I have a data set as below, for some products, during some months, the request quantities are missing. I know they are 0.

For a given time periods (e.g. from Jun-2011 to Oct-2011), how to add those missing observation by programming?

ProductArea        Product_Level_1 Request_Period_DT  Request_Quantity

AAA                   bbb                    nov-2010                   100

AAA                   bbb                    mar-2011                   80

AAA                   bbb                    apr-2011                   100

AAA                   bbb                    may-2011                   90

AAA                   bbb                    sep-2011                   200 

AAA                   ccc                    jun-2011                    100

AAA                   ccc                    jul-2011                     50

AAA                   ccc                    aug-2011                   80

AAA                   ccc                    sep-2011                   90

BBB                   ddd                    jul-2011                    100

BBB                   eee                    mar-2011                   80

BBB                   eee                    apr-2011                   100

BBB                   eee                    may-2011                   90

BR//alex


Accepted Solutions
Solution
‎11-15-2011 01:20 PM
PROC Star
Posts: 7,356

How to add all missing observations into a data set?

There are a number of ways to do it, some of which (like using proc expand) require products beyond base sas.  One way, not the most efficient but easy to follow, is to use a datastep merge.  E.g.:

data have;

  input (ProductArea Product_Level_1) ($)

         Request_Period_DT anydtdte. Request_Quantity;

  format Request_Period_DT monyy7.;

  cards;

AAA  bbb  nov-2010 100

AAA  bbb  mar-2011  80

AAA  bbb  apr-2011 100

AAA  bbb  may-2011  90

AAA  bbb  sep-2011 200

AAA  ccc  jun-2011 100

AAA  ccc  jul-2011  50

AAA  ccc  aug-2011  80

AAA  ccc  sep-2011  90

BBB  ddd  jul-2011 100

BBB  eee  mar-2011  80

BBB  eee  apr-2011 100

BBB  eee  may-2011  90

;

/create a file with one record for each ProductArea Product_Level_1 combination*/

proc sort data=have out=missing nodupkey;

  by ProductArea Product_Level_1;

run;

/*create macro variables for the 2 dates of concern*/

%let first=01jun2011;

%let last=01oct2011;

/*expand the file missing to include a record for every month in the range*/

data missing;

  set missing;

  by ProductArea Product_Level_1;

  Request_Quantity=0;

  do Request_Period_DT="&first."d to "&last."d;

    if day(Request_Period_DT) eq 1 then output;

  end;

run;

/*merge have and the expanded missing*/

data want;

  merge missing (in=ina) have (in=inb);

  by ProductArea Product_Level_1 Request_Period_DT;

  if inb or (ina and not inb);

run;

View solution in original post


All Replies
Solution
‎11-15-2011 01:20 PM
PROC Star
Posts: 7,356

How to add all missing observations into a data set?

There are a number of ways to do it, some of which (like using proc expand) require products beyond base sas.  One way, not the most efficient but easy to follow, is to use a datastep merge.  E.g.:

data have;

  input (ProductArea Product_Level_1) ($)

         Request_Period_DT anydtdte. Request_Quantity;

  format Request_Period_DT monyy7.;

  cards;

AAA  bbb  nov-2010 100

AAA  bbb  mar-2011  80

AAA  bbb  apr-2011 100

AAA  bbb  may-2011  90

AAA  bbb  sep-2011 200

AAA  ccc  jun-2011 100

AAA  ccc  jul-2011  50

AAA  ccc  aug-2011  80

AAA  ccc  sep-2011  90

BBB  ddd  jul-2011 100

BBB  eee  mar-2011  80

BBB  eee  apr-2011 100

BBB  eee  may-2011  90

;

/create a file with one record for each ProductArea Product_Level_1 combination*/

proc sort data=have out=missing nodupkey;

  by ProductArea Product_Level_1;

run;

/*create macro variables for the 2 dates of concern*/

%let first=01jun2011;

%let last=01oct2011;

/*expand the file missing to include a record for every month in the range*/

data missing;

  set missing;

  by ProductArea Product_Level_1;

  Request_Quantity=0;

  do Request_Period_DT="&first."d to "&last."d;

    if day(Request_Period_DT) eq 1 then output;

  end;

run;

/*merge have and the expanded missing*/

data want;

  merge missing (in=ina) have (in=inb);

  by ProductArea Product_Level_1 Request_Period_DT;

  if inb or (ina and not inb);

run;

Occasional Contributor
Posts: 5

How to add all missing observations into a data set?

Hi art297, thank you very much for your kindly answerSmiley Happy, it is very clear and this is exactly what I am looking for!  I'll test it in system.

I am very new in sas programming, is it possible for you to explain a little bit more for others solutions (like using proc expand) in brief?

PROC Star
Posts: 7,356

How to add all missing observations into a data set?

I'll have to leave that for someone more versed than I with proc expand.

SAS Employee
Posts: 416

Re: How to add all missing observations into a data set?

Hello -

Here is an example of how to use PROC TIMESERIES (of SAS/ETS) to create time series data from your original data (including 0 values for dates which are missing in your data).

I was assuming that you would like to see all series to be of similar length - otherwise, just leave out the START and END option.

Thanks,

Udo

data have;

  input (ProductArea Product_Level_1) ($)

         Request_Period_DT anydtdte. Request_Quantity;

  format Request_Period_DT monyy7.;

  cards;

AAA  bbb  nov-2010 100

AAA  bbb  mar-2011  80

AAA  bbb  apr-2011 100

AAA  bbb  may-2011  90

AAA  bbb  sep-2011 200

AAA  ccc  jun-2011 100

AAA  ccc  jul-2011  50

AAA  ccc  aug-2011  80

AAA  ccc  sep-2011  90

BBB  ddd  jul-2011 100

BBB  eee  mar-2011  80

BBB  eee  apr-2011 100

BBB  eee  may-2011  90

;

proc sort data=have;

by ProductArea Product_Level_1 Request_Period_DT;

run;

proc timeseries data=have out=want;

id Request_Period_DT interval=month accumulate=total start="01NOV2010"d end="01SEP2011"d setmissing=0;

var Request_Quantity;

by ProductArea Product_Level_1;

run;

Occasional Contributor
Posts: 5

How to add all missing observations into a data set?

Hi Udo, it works perfect, thanks a lot! Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2175 views
  • 5 likes
  • 3 in conversation