topic Re: move one row up for all variables in New SAS User

move one row up for all variables

sarahfung532 — Thu, 13 Feb 2020 21:55:39 GMT

Hi,

I have a big data with hundreds variables, each variable has a missing value that across all the variables diagonally. The data looks like:

ID X1 X2 X3 X4 X5 ...... Xn

1 . 2.1 3.1 4.1 5.1 .....

2 1.2 . 3.3 4.5 5.3

3 1.3 2.2 . 4.4 5.5

4 1.4 2.3 3.6 . 5.1

5 1.8 2.9 3.0 4.7 .

...

;

How could I move one row up for each variable (one row less in the end), and the result I wanted is:

ID X1 X2 X3 X4 X5 ...... Xn

1 1.2 2.1 3.1 4.1 5.1 .....

2 1.3 2.2 3.3 4.5 5.3

3 1.4 2.3 3.6 4.4 5.5

4 1.8 2.9 3.0 4.7 5.1

...

;

Re: move one row up for all variables

Astounding — Thu, 13 Feb 2020 22:06:52 GMT

This isn't very elegant, but it will work:

data want;
   set have (keep=x1 where=(x1 > .));
   set have (keep=x2 where=(x2 > .));
   ...
   set have (keep=xn where=(xn > .));
run;

Re: move one row up for all variables

sarahfung532 — Thu, 13 Feb 2020 22:25:56 GMT

Thank you for your quick response! It would work for small data set. But my data have 10,000 observations and over 800 same type of variables (x1 to x800), so the "set" statement won't be so practical in here ...

Re: move one row up for all variables

novinosrin — Thu, 13 Feb 2020 22:35:39 GMT

HI @sarahfung532 Is your ID numeric? If yes, i will take a shot a with multi dim array. If not, I have to think of something different

Re: move one row up for all variables

Astounding — Thu, 13 Feb 2020 22:37:09 GMT

It becomes more practical if you let macro language do the typing. For example:

%macro nonmissing (n=);
   %local i;
   %do i=1 %to &n;
      set have (keep=x&i where=(x&i > .));
   %end;
%mend nonmissing;

data want;
   %nonmissing (n=800)
run;

Re: move one row up for all variables

Tom — Thu, 13 Feb 2020 22:45:28 GMT

Are you sure? That looks like some type of array with missing values along the diagonal. Are you sure that eliminating the missing values won't put the values in the wrong place? Are you sure that you shouldn't instead be moving the values one column to the left instead?

Re: move one row up for all variables

novinosrin — Thu, 13 Feb 2020 23:04:56 GMT

Hi again @sarahfung532 I am not sure how rolling up leading to exluding ID makes sense if you may clarify, nonetheless here is an approach



data have;
input ID   X1    X2     X3    X4    X5;
cards;
1    .        2.1    3.1    4.1    5.1 
2   1.2     .        3.3    4.5    5.3
3   1.3     2.2    .        4.4    5.5  
4   1.4     2.3    3.6     .       5.1
5   1.8     2.9    3.0    4.7    .
;

data want;
 do _n_=1 by 1 until(z);
  set have end=z;
  array t(5,5) _temporary_;
  array u x1-x5;
  do over u;
   t(_n_,_i_)=u;
  end;
 end;
 do _n_=1 to 5;
  do _i_=1 to dim(t,1)-1;
   if t(_i_,_n_)=. then do;
    t(_i_,_n_)=t(_i_+1,_n_);
    t(_i_+1,_n_)=.;
   end;
  end;
 end;
 do _n_=1 to dim(t,1)-1;
  do over u;
   u=t(_n_,_i_);
  end;
  output;
 end;
 drop id;
run;
/*And if you want the ID rolled up as it seems your output exludes ID5,
the following does it*/
data want_with_ID;
 do _n_=1 by 1 until(z);
  set have end=z;
  array t(5,5) _temporary_ ;
  array u x1-x5;
  array v(5) _temporary_;
  v(_n_)=id;
  do over u;
   t(_n_,_i_)=u;
  end;
 end;
 do _n_=1 to 5;
  do _i_=1 to dim(t,1)-1;
   if t(_i_,_n_)=. then do;
    t(_i_,_n_)=t(_i_+1,_n_);
    t(_i_+1,_n_)=.;
   end;
  end;
 end;
 do _n_=1 to dim(t,1)-1;
  id=v(_n_);
  do over u;
   u=t(_n_,_i_);
  end;
  output;
 end;
run;

And if your ID is char, please make the V array, a temp array with a $ length like

array v(5)$32 _temporary_;

Re: move one row up for all variables

sarahfung532 — Thu, 13 Feb 2020 23:15:25 GMT

The data actually came from a Deming Jackknife procedure, which the diagonal missing values are useless. We only need the non-missing values for further calculations.

Re: move one row up for all variables

sarahfung532 — Thu, 13 Feb 2020 23:17:09 GMT

Thank you! I will try that later.

Re: move one row up for all variables

sarahfung532 — Thu, 13 Feb 2020 23:27:15 GMT

I will try your code tomorrow. Thank you very much! The diagonal missing data was from a Jackknife procedure for Deming regression analysis. We don't care the ID match up or not, we need to do a matrix calculations after, which not allow missing values in there. That's why I need help to remove only the missing values. 🙂

Re: move one row up for all variables

novinosrin — Thu, 13 Feb 2020 23:30:41 GMT

"We don't care the ID match up or not,"

That makes it a piece of cake. Feel free to come back to us should you need any clarification tomorrow. It's no biggie. Of course the code provided by @Astounding is very convenient, however the multidim array isn't hard either once you know how to reference the elements. All the best!

Re: move one row up for all variables

Tom — Fri, 14 Feb 2020 00:57:51 GMT

@sarahfung532 wrote:

I will try your code tomorrow. Thank you very much! The diagonal missing data was from a Jackknife procedure for Deming regression analysis. We don't care the ID match up or not, we need to do a matrix calculations after, which not allow missing values in there. That's why I need help to remove only the missing values. 🙂

Still makes no sense to me. The normal meaning of a dataset is that a single observation represents that value of the variables for a single event/sample/person. Moving the values from the next observation for one of the variables makes no sense.

If you just need to eliminate the non-missing values for your matrix operations to work then replace the missing values with some constant. Like zero or 1 or the row mean or the column mean that will not impact the operation.

Re: move one row up for all variables

sarahfung532 — Fri, 14 Feb 2020 17:19:42 GMT

You code works beautifully!! 🙂 I tried it on my data which is 10,000 observations and over 50,000 variables, and the who procedure only took less than 2 minutes! Thank you so much!!!