topic Re: Get last non null value to first in SAS Programming

Get last non null value to first

melligeri — Fri, 29 Apr 2022 12:08:13 GMT

Hi all,

have

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

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

want

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

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

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

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 12:13:34 GMT

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

Re: Get last non null value to first

PeterClemmensen — Fri, 29 Apr 2022 12:13:43 GMT

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

Re: Get last non null value to first

melligeri — Fri, 29 Apr 2022 12:33:20 GMT

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

Re: Get last non null value to first

Ksharp — Fri, 29 Apr 2022 13:10:12 GMT

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;

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 13:10:35 GMT

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;

Re: Get last non null value to first

average_joe — Fri, 29 Apr 2022 13:13:17 GMT

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;

Re: Get last non null value to first

Tom — Fri, 29 Apr 2022 14:42:00 GMT

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

Re: Get last non null value to first

melligeri — Fri, 29 Apr 2022 14:28:41 GMT

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

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 14:31:56 GMT

Show me the modified code.

Re: Get last non null value to first

Tom — Fri, 29 Apr 2022 15:27:02 GMT

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;

Re: Get last non null value to first

melligeri — Fri, 29 Apr 2022 15:13:12 GMT

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.

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 15:16:49 GMT

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.

Re: Get last non null value to first

melligeri — Fri, 29 Apr 2022 15:23:33 GMT

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).

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 15:25:46 GMT

@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?

Re: Get last non null value to first

melligeri — Fri, 29 Apr 2022 15:29:43 GMT

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.

Re: Get last non null value to first

Tom — Fri, 29 Apr 2022 15:27:42 GMT

Why not fix it BEFORE transposing?

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 15:45:00 GMT

@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.

Re: Get last non null value to first

melligeri — Fri, 29 Apr 2022 15:46:40 GMT

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

Re: Get last non null value to first

PaigeMiller — Fri, 29 Apr 2022 15:57:40 GMT

@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.