DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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

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


Accepted Solutions
Solution
‎09-09-2016 11:01 AM
Super User
Posts: 10,500

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

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;

View solution in original post


All Replies
Solution
‎09-09-2016 11:01 AM
Super User
Posts: 10,500

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

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;
Occasional Contributor
Posts: 17

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

Perfect! Thank you very much for your hints! Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 237 views
  • 0 likes
  • 2 in conversation