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

[Escada]

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

[ballardw · Accepted solution]

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;

[Escada]

Perfect! Thank you very much for your hints! 🙂