topic Re: Putting in Zero for data blanks or gaps in data. in SAS Procedures

Putting in Zero for data blanks or gaps in data.

Jolly — Wed, 30 Jul 2014 16:23:33 GMT

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

Re: Putting in Zero for data blanks or gaps in data.

ballardw — Wed, 30 Jul 2014 16:27:36 GMT

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;

Re: Putting in Zero for data blanks or gaps in data.

Reeza — Wed, 30 Jul 2014 16:46:25 GMT

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;

Re: Putting in Zero for data blanks or gaps in data.

data_null__ — Wed, 30 Jul 2014 16:46:33 GMT

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.

Re: Putting in Zero for data blanks or gaps in data.

stat_sas — Wed, 30 Jul 2014 17:15:02 GMT

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;

Re: Putting in Zero for data blanks or gaps in data.

haikuobian — Wed, 30 Jul 2014 18:25:48 GMT

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

Re: Putting in Zero for data blanks or gaps in data.

Ksharp — Thu, 31 Jul 2014 14:21:35 GMT

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