BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
id value
1abc
1abc
1 
2pqr
2pqr
2 
2 
3mno
3mno

 

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

4 REPLIES 4
stat_sas
Ammonite | Level 13

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;

 

 

rogerjdeangelis
Barite | Level 11
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;

SASPhile
Quartz | Level 8

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

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 740 views
  • 2 likes
  • 4 in conversation