BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WiDem
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

View solution in original post

5 REPLIES 5
AhmedAl_Attar
Rhodochrosite | Level 12

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
Barite | Level 11

%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
Super User
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
Calcite | Level 5

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1388 views
  • 7 likes
  • 4 in conversation