CODEWITHSUNDEEP
sas
Sushant Shinde
Sushant Shinde

Reputation: 65

How to repeat or merge rows in sas

Col1 col2   Col3
1    Abc    Shs
2    Cuz    Dhsh
3    Uhhj   Wer
.           Xyz
.           Pqr
4    Yui    Pol
.           Lkj
5    Haha   Jaja
6    Euue   Suus
7    Shus   Yeye

I want to repeat col1 3rd record to be repeated in below two rows same for 4

Output I want

Col1 col2   Col3
1    Abc    Shs
2    Cuz    Dhsh
3    Uhhj   Wer
3    uhhj   Xyz
3    uhhj   Pqr
4    Yui    Pol
4    yui    Lkj
5    Haha   Jaja
6    Euue   Suus
7    Shus   Yeye

I tried using in Excel macros but not able achieve output in sas

Upvotes: 0

Views: 598

Answers (1)

Richard
Richard

Reputation: 27498

Use retain to maintain and track the value of the variables retrieved or assigned in the prior row.

data want;
  set have;

  retain pcol1 pcol2 pcol3;

  if missing(col1) then col1 = pcol1; 
  if missing(col2) then col2 = pcol2; 
  if missing(col3) then col3 = pcol3; 

  pcol1 = col1;
  pcol2 = col2;
  pcol3 = col3;

  drop pcol:;
run;

Two arrays can be used for the case of many columns:

  • Variable based array for referencing values from data set
  • Temporary array for tracking prior row values. Temporary arrays are not affected by standard DATA Step behavior that 'reset PDV to missing'.
data want;
  set have;

  array priors(1000) _temporary_;
  array values col1-col40;

  do _n_ = 1 to dim(values);  * repurpose _n_ for loop indexing;

    if missing(values(_n_)) 
      then values(_n_) = priors(_n_); * repeat prior value into missing value;
      else priors(_n_) = values(_n_); * track most recent non-missing value;

  end;        
run;

Upvotes: 2

Related Questions