Putting in Zero for data blanks or gaps in data.

[Jolly]

I am working on a Data set that has month complaints by products.  The data set is for the last 30 months and there are about 40 products.  The issue I have is that my export only includes data for when there are actually complaints. So my data set may end up looking like:

Jan12     2

Feb12     8

Mar12     3

Jun12     1

Aug12     4

Oct12     7

Nov12     3

Dec12     5

ect...

My data set has 3 columns, Product, Month Complaints Received, Number of Complaints.

The data set is about 400 rows long right now, but I need to include months for zero.  The proc shewhart procedure just draws a straight line between any gaps, but I need the control limits to actually take account for zeros in the data set.  So my desired data set would look like:

Jan12     2

Feb12     8

Mar12     3

Apr12     0

May12     0

Jun12     1

Jul12     0

Aug12     4

Sep12     0

Oct12     7

Nov12     3

Dec12     5

ect...

Thanks,

Jeff

[ballardw]

You don't mention the variable name but in the data step where you build the data add:

NumberOfComplaints = not(missing (NumberOfComplaints));

Or if you prefer:

if missing(NumberOfComplaints) then NumberOfComplaints=0;

[Reeza · Accepted solution]

Create an empty dataset that has all the months and then merge it in. 

data empty;

start='01Jan2012'd;

complaints=0;

do i=1 to 30;

date=intnx('month', start, i-1, 'b');

output;

end;

run;

data want;

merge empty (in=a) have;

by date;

if a;

run;

[data_null__]

Do you have SAS/ETS software.  If so you could use PROC EXPAND.  I don't so I can't make example. 

Or you could use features of PROC SUMMARY (CLASSDATA)  but your example data is insufficient to show complete example. 

There are may ways to add in the missing rows sort of depends on how you like to do it.

[stat_sas]

data have;
input month monyy5. complaint;
format month monyy5.;
datalines;
Jan12     2
Feb12     8
Mar12     3
Jun12     1
Aug12     4
Oct12     7
Nov12     3
Dec12     5
;

data all_months;
start='01Jan2012'd;
format month monyy5.;
do i=1 to 30;
month=intnx('month', start, i-1, 'b');
output;
end;
run;

proc sql;
create table want as
select a.month,coalesce(b.complaint,0) as complaints
from all_months a left join have b
on a.month=b.month;
quit;

[haikuobian]

Here is a one-step alternative:

data have;

     input month monyy5. complaint;

     format month monyy5.;

     datalines;

Jan12     2

Feb12     8

Mar12     3

Jun12     1

Aug12     4

Oct12     7

Nov12     3

Dec12     5

;

data want;

     merge have have(keep=month rename= (month=_mon) firstobs=2) end=last;

     do i=1 to intck('month',month,coalesce(_mon,month));

           output;

           month=intnx('month',month,1);

           complaint=0;

     end;

     if last then

           output;

run;

Haikuo

[Ksharp]

data have;
     input month monyy5. complaint;
     format month monyy5.;
     datalines;
Jan12     2
Feb12     8
Mar12     3
Jun12     1
Aug12     4
Oct12     7
Nov12     3
Dec12     5
;
data want(drop=_month);
     merge have have(keep=month rename= (month=_month) firstobs=2);
      output;
     do month=intnx('month',month,1) to intnx('month',ifn(_month=.,0,_month),-1);
       if day(month)=1 then do;complaint=0;output;end;
     end;
run;

Xia Keshan