CODEWITHSUNDEEP
sas
Robert Penridge
Robert Penridge

Reputation: 8513

SAS Retain / Fill Down in place with by groups

Wondering if there is an elegant way in SAS to go from the have dataset to the want dataset without creating additional variables (even temporary variables).

Basically the logic is to fill the values in a column down. Each new by-group re-initializes the process.

data have;
  input a $ val1 val2 val3;
  datalines;
a 1 . .
a . . .
a . 2 .
a . . .
a . . 3
a . . . 
b . . .
b . 5 .
b . . .
;
run;

Desired results:

data want;
  input a $ val1 val2 val3;
  datalines;
a 1 . .
a 1 . .
a 1 2 .
a 1 2 .
a 1 2 3
a 1 2 3 
b . . .
b . 5 .
b . 5 .
;
run;

Upvotes: 1

Views: 964

Answers (1)

Tom
Tom

Reputation: 51621

You can use the UPDATE statement to do this. The UPDATE statement is designed to apply transactions to a source dataset that has a unique key. You can use your single dataset as both the source and the transaction data by using OBS=0 dataset option so you are starting with an empty source dataset. If you add an explicit output statement you can create your desired result instead of the normal one observation per group.

data want;
  update have(obs=0) have;
  by a ;
  output;
run;

Upvotes: 2

Related Questions