Get last non null value to first

[melligeri]

Sorry about lack of information.
Any help is appreciated, Thank you.

[PaigeMiller]

---

@melligeri wrote:
Sorry about lack of information.
Any help is appreciated, Thank you.

---

Please write complete and clear specifications for the problem, with several examples (plural), so we don't have to keep asking you for more information. Spend some time on it. Be thorough. Emphasis on COMPLETE and emphasis on CLEAR.

[Tom]

Why not fix it BEFORE transposing?

[Tom]

Let's assume by 'null' you mean missing.

data have ;
  input id (val1-val8) ($);
cards;
1 abc efg hij klm npo qrs tuv wxy
2 hdi iijk klm . . . . .
3 kkk iikl . . . . . .
4 klm . . . . . . .
;

If you literally have the string 'null' in the variable values then adjust the code below to account for that.

You could unroll the data out into a "tall" dataset instead of the current "wide" dataset.  Then roll it back into the array.

data tall;
  set have;
  array x val1-val8;
  do index=dim(x) to 1 by -1;
    value=x[index];
    if not missing(value) then output;
  end;
  keep id value;
run;

data want;
 do index=1 by 1 until(last.id);
   merge have tall;
   by id;
   array x val1-val8;
   if first.id then call missing(of x[*]);
   x[index]=value;
 end;
 drop index value ;
run;

If the data is large then you might want to make the TALL dataset as a VIEW instead.

Result:

Obs    id    val1    val2    val3    val4    val5    val6    val7    val8

 1      1    wxy     tuv     qrs     npo     klm     hij     efg     abc
 2      2    klm     iijk    hdi
 3      3    iikl    kkk
 4      4    klm

[melligeri]

Hi,
Thank you for your reply.
This is slight change in the requirement. Sorry i wasnt clear on the first time.

data have ;
input id (val1-val9) ($);
cards;
1 abc efg hij klm npo qrs tuv wxy aaa
2 hdi iijk klm . . . . . .
3 kkk iikl . . . . . . .
4 klm . . . . . . . .
;

The output should have data from val9 to val2.
Basically i need only 8 values but they should be latest, meaning it has to drop val1.

[Tom]

If the values are really missing (instead of the silly "null" string in your example) then you could use CATX() to help you.  Make sure to define the string variable long enough to hold the values.

data want;
  set have;
  array x val1-val8 ;
  length string $200 ;
  string=catx('|',of val8-val1);
  do index=1 to dim(x);
    x[index]=scan(string,index,'|');
  end;
  drop index string;
run;

If you really have the "null" text in the fields then remove the trailing "null" text first.  You can use COALESCEC() to add it back it you want.

data want;
  set have;
  array x val1-val8 ;
  do index=dim(x) to 1 by -1 while (x[index]='null');
     call missing(x[index]);
  end;
  length string $200 ;
  string=catx('|',of val8-val1);
  do index=1 to dim(x);
    x[index]=coalescec(scan(string,index,'|'),'null');
  end;
  drop index string;
run;