Help using Base SAS procedures

Putting in Zero for data blanks or gaps in data.

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Putting in Zero for data blanks or gaps in data.

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


Accepted Solutions
Solution
‎07-30-2014 12:46 PM
Super User
Posts: 17,829

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

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;

View solution in original post


All Replies
Super User
Posts: 10,500

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

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;

Solution
‎07-30-2014 12:46 PM
Super User
Posts: 17,829

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

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;

Respected Advisor
Posts: 3,777

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

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.

Trusted Advisor
Posts: 1,204

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

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;

Occasional Contributor
Posts: 17

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

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

Super User
Posts: 9,681

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 284 views
  • 2 likes
  • 7 in conversation