Filling for missing data

[WiDem]

Hi all,

I have quarterly data that is sparse that I am hoping to use SAS to fill.  I devised this process in Excel but it is currently a very slow and manual process.  I would like to move it to SAS and hopefully make it easy enough to refresh periodically.

Basically, I want to take some sparse data (i.e. missing months) and either fill backward (or forward if that's easier) from the data points I do have in order to fill in desired months.

In Excel I did this by forming the data into a grid (one row per client, one column per month) and reading from the data where the grid was nonzero, and pulling from the right if the data was non-zero.  But I'm thinking this may not be the best way to do it in SAS.

Does anyone have any ideas for general strategy to do this?

Thanks!

--------------------

Example data would look like the following:

Table 1

Client     Year     Month     Premium

A           2012        3            5000     

A           2012        6            5500

A           2012       12           5250

A           2013        3            5400

A           2013        6            5000

A           2013        9            5100

I have another table that might tell me that the effective months for our partnership with client A were December 2011 through August 2013.

In this case, I'd like to see the following information in a new  (i.e. as records in the format of Table 1)

Yr/Mo       Premium

12/2011    5000 (assume this didn't show up in data, I want to fill back)

1/2012      5000 (filled back)

2/2012      5000 (filled back)

3/2012      5000 (showed up in data)

4/2012      5000 or 5500 (fill forward or backward)

5/2012      5000 or 5500 (fill forward or backward)

6/2012      5500 (from data)

7/2012      5500 or 5250 (fill forward or backward)

8/2012      5500 or 5250 (fill forward or backward)

9/2012      5500 or 5250 (fill forward or backward--note it's missing from Table 1 data)

10/2012    5500 or 5250 (fill forward or backward)

11/2012    5500 or 5250 (fill forward or backward)

12/2012    5250 (from data)

1/2013      5250 or 5400 (fill forward or backward)

...

6/2013      5000 (from data)

7/2013      5000 or 5100 (fill forward or backward)

8/2013      5000 or 5100 (fill forward or backward)

Note that there should be no entry for September 2013, as it is past the relationship period--the system providing my data can be a bit slow.

[AhmedAl_Attar]

Couldn't you use the Merge statement?

Use your other table, which tells you the effective months for your partnership with client A were December 2011 through August 2013, as a base, and overlay it the new quarter data set.

Example:

data base (keep=client year month premium);

    length    client $1

            year 3

            month 3

            premium 3;

    do client ='A','B','C','D';

        do y=2011 to 2013;

            year = y;

            do m=1 to 12;

                month = m;

                premium=5250;

                output;

            end;

        end;

    end;

run;

data new;

    length    client $1

            year 3

            month 3

            premium 3;

    input    client $

            year

            month

            premium;

datalines;

A 2012 3 5000    

A 2012 6 5500

A 2012 12 5250

A 2013 3 5400

A 2013 6 5000

A 2013 9 5100

;

run;

data want;

    Merge base(where=(client='A')) new;

    by client year month;

run;

[mohamed_zaki]

%let start_date = 01jan2012;

%let end_date = 01aug2013;

%let months = intck('month',"&start_date."d,"&end_date."d);

data dates (drop= m date);

do m = 0 to &months.;

date = intnx('month',"&start_date."d,m,'s');

Month=month(date);

Year=year(date);

output;

end;

run;

data have ;

input Year     Month     Premium;

cards;

2012 3 

2012 6 5500

2012 12 5250

2013 3 5400

2013 6 5000

2013 9 5100

;

run;

proc sql ;

create table want as

select a.year, a.month, b.premium 

from work.dates a LEFT JOIN work.have b

on (a.year = b.year AND a.month = b.month)

;run;

data want ;

if _n_=1 then do;

do until(premium);

  set want;

  if not missing(premium) then want_value=premium;

end;

end;

do until(last);

  set want end=last;

  if premium then want_value= premium;

  output;

end;

run;

[Ksharp · Accepted solution]

data have;
 input Client  $   Year     Month     Premium ;
cards;
A           2012        3            5000     
A           2012        6            5500
A           2012       12           5250
A           2013        3            5400
A           2013        6            5000
A           2013        9            5100
;
run;
data want;
 set have;
 retain start '01nov2011'd;
 do i=1 to intck('month',start,mdy(month,1,year));
  date=intnx('month',start,i);
  output;
 end;
 start=mdy(month,1,year);
 format date mmyys7.;
 drop start i;
run;

Xia Keshan

Message was edited by: xia keshan Fix an error

[WiDem]

That works great. I will have to work to understand it as I'm total scrub at SAS.

I was able to come up with an OK but unsatisfying way using offset joins to self in SQL but if we're moving to SAS I really need to learn how to use the tools we have.

[WiDem]

Thanks, everyone, for the ideas/solutions you offered.