topic How to fill missing values variable with previous values, based on groups? in SAS Programming

How to fill missing values variable with previous values, based on groups?

Escada — Fri, 09 Sep 2016 13:30:36 GMT

Dear all,

I have the following data set, a set of entities, for each I have assigned an Indicator with the value 1, depending on the Period:

Entity Period Indicator

A 31DEC2014

A 30MAR2015 1

A 30JUN2015

A 30SEP2015

B 31DEC2014 1

B 31MAR2015

B 30JUN2015

C 31DEC2014

C 31MAR2015

C 30JUN2015 1

C 30SEP2015

D 31MAR2015 1

D 30JUN2015

I want to replicate, for subsequent periods and for entiy, the indicator assignment 1, when missing, leaving the previous periods for entity, empty. Shoud look like the following:

Entity Period Indicator

A 31DEC2014

A 30MAR2015 1

A 30JUN2015 1

A 30SEP2015 1

B 31DEC2014 1

B 31MAR2015 1

B 30JUN2015 1

C 31DEC2014

C 31MAR2015

C 30JUN2015 1

C 30SEP2015 1

D 31MAR2015 1

D 30JUN2015 1

Any hint on how to solve this issue?

Thank you

Re: How to fill missing values variable with previous values, based on groups?

ballardw — Fri, 09 Sep 2016 14:41:42 GMT

RETAIN lets you keep values from observation to observation. FIRST processing when using By group allows conditional assignment based on start of a group of related records.

This works for your example data:

data have;
   informat Entity $4. period date9. indicator best4.;
   format period date9.;
   input Entity Period   Indicator   ;
datalines;
A    31DEC2014     .
A    30MAR2015     1
A    30JUN2015     .
A    30SEP2015     .
B    31DEC2014     1
B    31MAR2015     .
B    30JUN2015     .
C    31DEC2014     .
C    31MAR2015     .
C    30JUN2015     1
C    30SEP2015     .
D    31MAR2015     1
D    30JUN2015     .
;
run;

data want;
   set have;
   by notsorted entity;
   Retain LastIndicator;
   if first.entity then LastIndicator=.;
   if indicator ne . then LastIndicator=indicator;
   else if indicator=. then Indicator=LastIndicator;

   drop LastIndicator;
run;

Re: How to fill missing values variable with previous values, based on groups?

Escada — Fri, 09 Sep 2016 15:02:36 GMT

Perfect! Thank you very much for your hints! 🙂