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?
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;
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;
So this works only if the last values is missing? Not for those values missing in between or first value missing
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.