filling in the gaps [SAS]

Prev: proc univariate?
Next: macro parameter withing the variable while using into or call symput

From: Doug on 21 Apr 2010 08:35

Hi

Its been a long time since I programmed - so i am a bit rusty.

I have a dataset that contains deltas (only populated when a value
changes). The dataset has an id, a weekending date, and the value of
the value that changes. All items are initialised at the beginnning
of the quarter. I need to fill in the gaps between the first week
(when the value is set) to the first delta, and then keep the delta
value until the second delta and so on.
My file looks something like this:
data test;
format dater date9.;
input dateEvent date9. id val $;
datalines;
7-Jul-09 1 a
8-Sep-09 2 a
29-Sep-09 2 a
7-Jul-09 3 a
18-Aug-09 3 a
4-Aug-09 1 b
18-Aug-09 2 b
28-Jul-09 3 b
22-Sep-09 3 b
22-Sep-09 1 c
28-Jul-09 2 c
4-Aug-09 3 c
29-Sep-09 1 d
7-Jul-09 2 d
;
run;

What I would like is to have a file that has, for the id of 1,:
7-Jul-09 1 a
14-Jul-09 1 a
21-Jul-09 1 a
28-Jul-09 1 a
4-Aug-09 1 b
11-Aug-09 1 b
18-Aug-09 1 b
25-Aug-09 1 b
1-Sep-09 1 b
8-Sep-09 1 b
15-Sep-09 1 b
22-Sep-09 1 c
29-Sep-09 1 d

I have tried to recall old code that uses ratains and first. items but
I seem to be getting nowhere.

In sumary, I want to populate the missing weeks for each record and
then retain the value for each Id until it changes. any assistance
greately appreciated.

Doug

From: data _null_; on 21 Apr 2010 14:32

On Apr 21, 7:35 am, Doug <jerrabombe...(a)gmail.com> wrote:
> Hi
>
> Its been a long time since I programmed - so i am a bit rusty.
>
> I have a dataset that contains deltas (only populated when a value
> changes). The dataset has an id, a weekending date, and the value of
> the value that changes. All items are initialised at the beginnning
> of the quarter. I need to fill in the gaps between the first week
> (when the value is set) to the first delta, and then keep the delta
> value until the second delta and so on.
> My file looks something like this:
> data test;
> format dater date9.;
> input dateEvent date9. id val $;
> datalines;
> 7-Jul-09 1 a
> 8-Sep-09 2 a
> 29-Sep-09 2 a
> 7-Jul-09 3 a
> 18-Aug-09 3 a
> 4-Aug-09 1 b
> 18-Aug-09 2 b
> 28-Jul-09 3 b
> 22-Sep-09 3 b
> 22-Sep-09 1 c
> 28-Jul-09 2 c
> 4-Aug-09 3 c
> 29-Sep-09 1 d
> 7-Jul-09 2 d
> ;
> run;
>
> What I would like is to have a file that has, for the id of 1,:
> 7-Jul-09 1 a
> 14-Jul-09 1 a
> 21-Jul-09 1 a
> 28-Jul-09 1 a
> 4-Aug-09 1 b
> 11-Aug-09 1 b
> 18-Aug-09 1 b
> 25-Aug-09 1 b
> 1-Sep-09 1 b
> 8-Sep-09 1 b
> 15-Sep-09 1 b
> 22-Sep-09 1 c
> 29-Sep-09 1 d
>
> I have tried to recall old code that uses ratains and first. items but
> I seem to be getting nowhere.
>
> In sumary, I want to populate the missing weeks for each record and
> then retain the value for each Id until it changes. any assistance
> greately appreciated.
>
> Doug

This method assumes the date range is the same for all subjects.

1) determine the range.
2) create weekly obs
3) expand the data
4) locf

From: data _null_; on 21 Apr 2010 14:32

On Apr 21, 1:32 pm, "data _null_;" <datan...(a)gmail.com> wrote:
> On Apr 21, 7:35 am, Doug <jerrabombe...(a)gmail.com> wrote:
>
>
>
>
>
> > Hi
>
> > Its been a long time since I programmed - so i am a bit rusty.
>
> > I have a dataset that contains deltas (only populated when a value
> > changes). The dataset has an id, a weekending date, and the value of
> > the value that changes. All items are initialised at the beginnning
> > of the quarter. I need to fill in the gaps between the first week
> > (when the value is set) to the first delta, and then keep the delta
> > value until the second delta and so on.
> > My file looks something like this:
> > data test;
> > format dater date9.;
> > input dateEvent date9. id val $;
> > datalines;
> > 7-Jul-09 1 a
> > 8-Sep-09 2 a
> > 29-Sep-09 2 a
> > 7-Jul-09 3 a
> > 18-Aug-09 3 a
> > 4-Aug-09 1 b
> > 18-Aug-09 2 b
> > 28-Jul-09 3 b
> > 22-Sep-09 3 b
> > 22-Sep-09 1 c
> > 28-Jul-09 2 c
> > 4-Aug-09 3 c
> > 29-Sep-09 1 d
> > 7-Jul-09 2 d
> > ;
> > run;
>
> > What I would like is to have a file that has, for the id of 1,:
> > 7-Jul-09 1 a
> > 14-Jul-09 1 a
> > 21-Jul-09 1 a
> > 28-Jul-09 1 a
> > 4-Aug-09 1 b
> > 11-Aug-09 1 b
> > 18-Aug-09 1 b
> > 25-Aug-09 1 b
> > 1-Sep-09 1 b
> > 8-Sep-09 1 b
> > 15-Sep-09 1 b
> > 22-Sep-09 1 c
> > 29-Sep-09 1 d
>
> > I have tried to recall old code that uses ratains and first. items but
> > I seem to be getting nowhere.
>
> > In sumary, I want to populate the missing weeks for each record and
> > then retain the value for each Id until it changes. any assistance
> > greately appreciated.
>
> > Doug
>
> This method assumes the date range is the same for all subjects.
>
> 1) determine the range.
> 2) create weekly obs
> 3) expand the data
> 4) locf- Hide quoted text -
>
> - Show quoted text -

Opps forgot the program

data test;
input dateEvent:date. id val $;
format dateEvent date9.;
datalines;
7-Jul-09 1 a
8-Sep-09 2 a
29-Sep-09 2 a
7-Jul-09 3 a
18-Aug-09 3 a
4-Aug-09 1 b
18-Aug-09 2 b
28-Jul-09 3 b
22-Sep-09 3 b
22-Sep-09 1 c
28-Jul-09 2 c
4-Aug-09 3 c
29-Sep-09 1 d
7-Jul-09 2 d
;
run;
proc sort;
by id dateEvent;
run;
proc summary data=test;
output out=range(drop=_:) max(dateEvent)=max min(dateEvent)=min;
run;
data classdata;
if 0 then set test(keep=dateevent);
set range;
do w=0 to intck('WEEK',min,max);
dateevent = intnx('WEEK',min,w,'sameday');
output;
end;
stop;
run;
proc print;
run;
proc summary data=test classdata=classdata nway;
by id;
class date:;
output out=expanded(drop=_:) idgroup(out(val)=);
run;
data locf;
do until(last.id);
set expanded;
by id;
length locf $1.;
locf = coalesceC(val,locf);
output;
end;
run;
proc print;
run;

|
Pages: 1
Prev: proc univariate?
Next: macro parameter withing the variable while using into or call symput