topic Re: P0pulate values for missing values in SAS Programming

P0pulate values for missing values

SASPhile — Mon, 30 Jan 2017 00:27:11 GMT

id	value
1	abc
1	abc
1
2	pqr
2	pqr
2
2
3	mno
3	mno

How to populate abc for missing values for Id 1, pqr for missing values for id 2?

Re: P0pulate values for missing values

stat_sas — Mon, 30 Jan 2017 00:48:05 GMT

Hi,

There are various approaches to do that. Please see the following using data step;

/* data step */

Assuming data is sorted by id variable. If not then you need to sort dataset by id

data want(drop=value rename=(v=value));
set have;
by id;
retain v;
if first.id then v=value;
run;

Re: P0pulate values for missing values

rogerjdeangelis — Mon, 30 Jan 2017 00:58:36 GMT

This is called LVCF last value carried forward

HAVE
====

Up to 40 obs WORK.HAVE total obs=9

Obs ID    VALUE

 1   1     abc
 2   1     abc
 3   1
 4   2     pqr
 5   2     pqr
 6   2
 7   2
 8   3     mno
 9   3     mno

WANT
====

Up to 40 obs WORK.WANT total obs=9

Obs ID    VALUE

 1   1     abc
 2   1     abc
 3   1     abc
 4   2     pqr
 5   2     pqr
 6   2     pqr
 7   2     pqr
 8   3     mno
 9   3     mno

WORKING CODE
============

 retain value_sav '   ';
 if value ne '' then value_sav=value;
 value=value_sav;

SOLUTION
========

* create some daat
data have;
format id 1. value $3.;
input
id @1 id value 3-5 ;
cards4;
1 abc
1 abc
1
2 pqr
2 pqr
2
2
3 mno
3 mno
;;;;
run;quit;

data want;
 retain value_sav '   ';
 set have;
 if value ne '' then value_sav=value;
 value=value_sav;
 drop value_sav;
run;quit;

Re: P0pulate values for missing values

SASPhile — Mon, 30 Jan 2017 15:14:45 GMT

So this works only if the last values is missing? Not for those values missing in between or first value missing

Re: P0pulate values for missing values

art297 — Mon, 30 Jan 2017 16:01:16 GMT

Both proposed methods appear to do what you want as long as the first.id isn't missing. Of course, you could correct for that by first sorting your data by both id and the variable of interest in descending order.

Art, CEO, AnalystFinder.com