Get last non null value to first

[melligeri]

Hi all,

have

id | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8

1 | abc  | efg   | hij    | klm  | npo |  qrs  | tuv   | wxy

2 | hdi   | iijk   | klm   | null | null |  null | null | null 

3 | kkk  | iikl  | null | null |  null | null | null  | null

4 | klm  | null | null |  null | null | null | null  | null

want

id | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8

1  | wxy  | tuv   | qrs   | npo  | klm |  hij  | efg   | abc

2 | klm   | iijk    | hdi  | null | null |  null | null | null

3 | iikl    | kkk   | null  | null | null  | null | null  | null

4 | klm  | | null  | null | null  | null | null  | null | null

Can anyone suggest me sas code to get the desired output.

[PaigeMiller]

Can you please provide an explanation of the logic that turns the first table into the second table?

[melligeri]

Thank you for your reply,

As you can see the have data set has multiple cal columns. Since this is transposed data that i have it can have any number of val columns. My requirement is, I need the last column value(if its not null) as the first column value in the want data set. Some id can have all the val columns with data, some can have only few val columns with data.

example:

for id = 1 in have data set val8 is populated in val1 of want data

similarly for id = 2 in have dataset val4 is populated in val1 of want data

Hope i am clear with my explanation

[average_joe]

So you just want the last non-null value as the first value. You are not concerned about the order of the remaining values.

data want;
set have;
array val(*) val1-val8;
do _i = 1 to 8;
    if val(_i) = 'null' then do;
        * swap the last non-null value with the first;
        _tmp = val(_i-1);
        val(_i-1) = val1;
        val1 = _tmp;
        leave;
    end;
end;
drop _:;
run;

[PeterClemmensen]

So you want to reverse all elements that are not null, correct?

[Ksharp]

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

data want;
 set have;
 array v{8} $ val:;
 array new{8} $ 40 ;
 j=0;
 do i=dim(v) to 1 by -1;
   if v{i} ne 'null' then do;j+1;new{j}=v{i};end;
    else new{i}=v{i};
 end;
drop i j val:;
run;

[PaigeMiller]

Here is my solution for VAR1-VAR3, you can modify this for any arbitrary number of variables. Assumes the missing/null values are always at the end.

data want;
    set have;
    array x var1-var3;
    array z(3) $ _temporary_;
    n=3-cmiss(of x(*)); 
    do i=n to 1 by -1;
        z(n-i+1)=x(i);
    end;
    do i=1 to dim(x);
        if not missing(x(i)) then x(i)=z(i);
    end;
    drop n i;
run;

[melligeri]

Hi,
thank you so much for the solution, but it is still not what i was expecting. I am sorry about the requirement i posted, i think it is not clear yet.
Let me try to explain in more here.

According to your code, it is only with data set having 3 val columns. I modified a bit, please find it below.

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

There are 9 val columns and i just want 8 val columns in the want dataset. So in the output i want something like this.

want
id | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8

1 aaa | wxy | tuv | qrs | npo | klm | hij | efg

2 | klm | iijk | hdi | null | null | null | null | null

3 | iikl | kkk | null | null | null | null | null | null

4 | klm | | null | null | null | null | null | null | null

Thank you for your help

[PaigeMiller]

Show me the modified code.

[melligeri]

data want;
set have;
array x val1-val8;
array z(8) $ _temporary_;
n=8-cmiss(of x(*));
do i=n to 1 by -1;
z(n-i+1)=x(i);
end;
do i=1 to dim(x);
if not missing(x(i)) then x(i)=z(i);
end;
drop n i;
run;

Thank you.

[PaigeMiller]

Maybe I misunderstood something here, but I was assuming where you wrote null it was actually a missing value. The text null is not a missing value. The code I wrote works fine if the values are actually missing.

[melligeri]

the word 'null' is actually missing values.

The requirement is actually to have the latest 8 values and ignore old value.

Example,
If there are val1-val9 columns in the output i need val9-val2 values.

The have dataset is actually transposed dataset, sometimes it can have just 4 columns(val1-val4) sometimes it can have 9 columns(val1-val9).

[PaigeMiller]

---

@melligeri wrote:
the word 'null' is actually missing values.

The requirement is actually to have the latest 8 values and ignore old value.

Example,
If there are val1-val9 columns in the output i need val9-val2 values.

The have dataset is actually transposed dataset, sometimes it can have just 4 columns(val1-val4) sometimes it can have 9 columns(val1-val9).

---

Are these variables numeric? Or are these variables character?

[melligeri]

character.

Basically it showul identify number of val columns.

example if there val columns till val12 it has to get the values from val12 to val5.

if it has val columns till val4 it has to get the values from val4- val1.

[PaigeMiller]

---

@melligeri wrote:

character.

---

So the code should work if you change 'null' to truly missing before you run my code.

Basically it showul identify number of val columns.

example if there val columns till val12 it has to get the values from val12 to val5.

if it has val columns till val4 it has to get the values from val4- val1.

This seems to be a new criterion that has not been mentioned before, and my code will not handle this.