topic Re: Filling for missing data in SAS Procedures

Filling for missing data

WiDem — Mon, 12 Jan 2015 20:17:05 GMT

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.

Re: Filling for missing data

AhmedAl_Attar — Mon, 12 Jan 2015 21:10:10 GMT

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;

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;

Re: Filling for missing data

mohamed_zaki — Mon, 12 Jan 2015 22:22:30 GMT

%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;

do until(last);

set want end=last;

if premium then want_value= premium;

output;

end;

run;

Re: Filling for missing data

Ksharp — Tue, 13 Jan 2015 08:41:58 GMT

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

Re: Filling for missing data

WiDem — Tue, 13 Jan 2015 16:16:58 GMT

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.

Re: Filling for missing data

WiDem — Tue, 13 Jan 2015 16:17:32 GMT

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