topic Re: Creating observations for missing values in SAS Procedures

Creating observations for missing values

endofline — Wed, 16 Aug 2017 20:17:09 GMT

Hi All,

I'm working with some price data that is summed up by the year and the fiscal quarter. However, not all observations have purchases in all quarters/years. I want to make those missing quarters/year appear as a 0. I have a 3 year bubble around each of my observations where I have data on what they spent.

Currently this is how my data looks

ID Year Qtr Spent

1 2010 1 50

1 2010 2 100

1 2010 4 25

1 2011 2 100

1 2011 4 200

What I want is:

ID Year Qtr Spent

1 2010 1 50

1 2010 2 100

1 2010 3 0

1 2010 4 25

1 2011 1 0

1 2011 2 100

1 2011 3 0

1 2011 4 200

1 2012 1 0

1 2012 2 0

1 2012 3 0

1 2012 4 0

Thanks in advance!

Re: Creating observations for missing values

Reeza — Wed, 16 Aug 2017 20:23:18 GMT

If you have SAS/ETS look at PROC TIMESERIES

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 20:29:05 GMT

I believe that I have proc timeseries, but have never used it. How would you use it to create what I need?

Re: Creating observations for missing values

Astounding — Wed, 16 Aug 2017 20:29:09 GMT

If you have to do it yourself without ETS, it's not that lengthy. First, create a data set with all combinations of ID, YEAR, and QTR:

proc sql noprint;

create table year_list as select distinct year from have;

create table id_list as select distinct id from have;

create table qtr_list as select distinct qtr from have;

create table all_combinations as select * from year_list, id_list, qtr_list;

quit;

Then sort, merge, fill in the zeros:

proc sort data=all_combinations;

by id year qtr;

run;

proc sort data=have;

by id year qtr;

run;

data want;

merge all_combinations have (in=have_data);

by id year qtr;

if have_data=0 then spent=0;

run;

Re: Creating observations for missing values

Reeza — Wed, 16 Aug 2017 20:31:17 GMT

You specify the time interval, the start and end period and it fills it in.

Make sure to include your BY statement as well.

Here's a fully worked example with fake data:

/*this is an example of time series data. 
1. Create a time series data set with missing intervals (IBM)
2. Add back missing entries using PROC TIMESERIES (IBM_NO_MISSING)
3. Calculate moving average - 12 month average
*/

/*1*/
data ibm;
	set sashelp.stocks;
	where stock='IBM';

	if month(date)=7 then
		delete;
run;

proc sort data=ibm;
	by date;
run;

/*2*/
proc timeseries data=ibm out=ibm_no_missing;
	id date interval=month start='01Aug1986'd end='01Dec2005'd;
	var open;
run;

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 20:45:33 GMT

For the example you posted where it has

start='01Aug1986'd end='01Dec2005'd;

Is this the overall global start and end date?

The way I get the data is that I have a 3 year bubble around each ID as they enter into my data. However, not all of them are entering in at the same time, some might enter in 2010 and others in 2013.

Re: Creating observations for missing values

Reeza — Wed, 16 Aug 2017 20:56:36 GMT

Ah...then this method won't work, you'll need to use the data step approach. If you have a data set that defines the bubbles it's relatively straightforward to loop the dates to get an empty table with all the records and then merge it in with the original table to get the results.

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 21:00:44 GMT

OK, how might you go about that then? I was thinking about something with loops but could not think of a solution.

Re: Creating observations for missing values

Reeza — Wed, 16 Aug 2017 21:01:55 GMT

What data do you have? If all you have is what you've shown above, how do you know what the start and end of the loop will be?

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 21:02:50 GMT

So this will work but it does still have some issues. My data goes from 2009 to 2015. but not all IDs are coming in during the same time frame. Some are coming in 2010 and others in 2013 and I just want to be able to capture the 3 year bubble around them. I tried this solution but it gives me the full 2009 to 2015 time frame around all of the IDs. Any thoughts on how to get it to just be around the 3 year bubble?

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 21:06:15 GMT

I have the variables I showed, ID, Yr, Qtr and Spent plus the start date of when they entered so:

ID Year Qtr Spent Date of Entry

1 2010 1 50 Jan 2011

1 2010 2 100 Jan 2011

1 2010 4 25 Jan 2011

1 2011 2 100 Jan 2011

1 2011 4 200 Jan 2011

So I have a year of lookback, the year during and the year after the entry date.

Re: Creating observations for missing values

Reeza — Wed, 16 Aug 2017 21:06:24 GMT

@endofline wrote:

Any thoughts on how to get it to just be around the 3 year bubble?

What's a 3 year bubble? As long as you can define it, you're fine, so far you haven't offered a definition.

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 21:10:34 GMT

ID Year Qtr Spent Date of Entry

1 2010 1 50 Jan 2011

1 2010 2 100 Jan 2011

1 2010 4 25 Jan 2011

1 2011 2 100 Jan 2011

1 2011 4 200 Jan 2011

So for the 3 year bubble I have a year of lookback, the year during and the year after the entry date.And I have this for each observation, with each obersvation having a different date of entry.

Re: Creating observations for missing values

Astounding — Wed, 16 Aug 2017 21:17:04 GMT

You'll have to describe the date ranges a little more. (And of course the program gets more complex.)

Could a company begin in Q3 of one year, and end in Q2 of another year? How do you know when the window begins and ends for each company? Does a window have to be exactly 12 quarters, or does it depend on the data?

I'll be able to compare my questions to your (just-posted) description later tonight.

Re: Creating observations for missing values

endofline — Wed, 16 Aug 2017 21:19:33 GMT

I know when they enter in the data set for sure by the date entered variable and that we're for sure suppose to be getting 1 year of lookback data from that entry date, the year during and the year after.

It does happen that someone does enter in the dataset in say April or October. However, it should be 12 qtrs.

Re: Creating observations for missing values

Shmuel — Wed, 16 Aug 2017 21:36:55 GMT

Use @Reeza's and @Astounding's logic but create the all_combinations with the next code

proc sql;
   create table temp1 as 
      select distinct id, date_of_entry;
quit;

data all_combinations;
  set temp1;
        start_year = year(date_of_entry) - 1; 
              /* change if needed accoring to date_of_entry format */
        do year = start_year to (start_year +2);
            do qtr=1 to 4;
                 output;
        end; end;
        keep id year qtr date_of_entry;   /* or drop start_year */
run;

continue with code of @Astounding (sort, merge, replace missing spent by 0).

Re: Creating observations for missing values

novinosrin — Wed, 16 Aug 2017 22:52:41 GMT

I'm afraid I haven't read through the thread as I am feeling very lazy. However, for your sample HAVE and WANT, and here you go:

data have;

input ID Year Qtr Spent;

datalines;

1 2010 1 50

1 2010 2 100

1 2010 4 25

1 2011 2 100

1 2011 4 200

;

data want;

do until(last.id);

do _n_=1 by 1 until(last.year);

set have;

by id year;

if first.year then _qtr=qtr;

if _n_=1 and qtr ne _n_ then

do;

_qtr=qtr;

_spent=spent;

do qtr=_n_ to qtr-1;

spent=0;

output;

end;

spent=_spent;

output;

_qtr=qtr;

end;

else if _qtr ne qtr then

do;

_spent=spent;

do qtr=_qtr+1 to qtr-1;

spent=0;

output;

end;

spent=_spent;

output;

_qtr=qtr;

end;

else output;

end;

drop _:;

run;

Re: Creating observations for missing values

Ksharp — Thu, 17 Aug 2017 13:43:26 GMT

It is very easy for SQL.

data have;
input ID   Year    Qtr   Spent ;
cards;
1    2010   1       50
1    2010   2      100 
1    2010   4        25
1    2011   2        100
1    2011   4        200
;
run;
data qtr;
 do qtr=1 to 4;
  output;
 end;
run;

proc sql;
create table want as
 select  a.*,coalesce(spent,0) as spent
  from
  (
select * from
(select distinct id from have),
(select distinct year from have),
(select distinct qtr from qtr)
) as a 
left join have as b
on a.id=b.id and a.year=b.year and a.qtr=b.qtr ;
quit;

Re: Creating observations for missing values

novinosrin — Thu, 17 Aug 2017 18:41:43 GMT

@Ksharp Thank you. It's something very intriguing to read your brilliant innovative solutions. I wish I had the drive to be keen, driven and dedicated as you are however I am too lazy to venture into much learning and applying. My bad. Thank you for making me think. Cheers!

Regards,

Naveen Srinivasan

Re: Creating observations for missing values

Reeza — Thu, 17 Aug 2017 19:14:24 GMT

@novinosrin your participation on here would indicate the opposite 😉

@novinosrin wrote:

I wish I had the drive to be keen, driven and dedicated as you are however I am too lazy to venture into much learning and applying. My bad. Thank you for making me think.