shift observations down accross columns

[aj69]

Hi

First let me say thank you for taking the time to help me with this.

I have the following data that looks like this

Var1	Var2	Var3	Var4
1	1	1	1
2	2	2	2
3	3	3	3
	4	4	3
	5	5
		6
		7

 (blanks on the bottom)

I would like the final output however to looke like this

Var1	Var2	Var3	Var4
		1
		2
	1	3
	2	4	1
1	3	5	2
2	4	6	3
3	5	7	3

 (blanks on top)

 Any thoughs on how to do this (if this is possible at all)? Can a do loop of some sort check for each column the number of rows with blanks (for the first column is 4), then using the final observation in a column, push it down by 4 and do the same for all the other observations in that column? Then do the same thing for all the other columns as well .

Thanks for the advice everyone.

[FreelanceReinh]

Hi @aj69,

Sure, this is possible. To find the optimal solution, it would be good to know:

1. How many observations and variables are in your real dataset?
2. Are Var1, ..., Var4 numeric or character variables or both?
3. Should the solution be flexible regarding the number of variables?
4. Are the non-missing values sorted in ascending order as in your example data?
5. Is it possible that missing values occur also between non-missing values and, if so, what should happen in this case?
6. What is the background/reason why you want to do this?

[aj69]

Hi FreelanceReinhard

• Are Var1, ..., Var4 numeric or character variables or both? The variables are actulally characters and combine an airline code, a destination, and the number of minutes from the top of the hour (eg ORD - AA - 05).
• Should the solution be flexible regarding the number of variables? the total variables are actually fixed at 24 (one for each hour of the day. The number of observations for each hour varies considerably depending on the airport I am getting the data from (takeoffs and landings). For example for Chicago, you may have only 40 departures at 7AM but 11AM is close to 90.

• Are the non-missing values sorted in ascending order as in your example data? The data is already sorted in a particular order (departure hour or arrival hour) and has to stay in that order.
• Is it possible that missing values occur also between non-missing values and, if so, what should happen in this case? No, due to the sorting, all the data will be together and the blanks will be together.
• What is the background/reason why you want to do this? Well, it is just a format standard really. I have written a SAS code to read in the departues and arrivals and list them as can be seen in the screenshot.  I could go to each column (24) and shift them down manually in Excel. I was just wondering if it is possible to do that within a SAS code.

 Thanks a bunch

[RW9]

Hi,

Interesting puzzel.  Simplest way is to tranpose the table to get values across, then loop in reverse over these moving things forward, then transpose the data back again.  You could of course replace the 7 in the below, with any given number, I just took that from your test data:

data have;
  Var1=1; Var2=1; Var3=1; Var4=1; output;
  Var1=2; Var2=2; Var3=2; Var4=2; output;
  Var1=3; Var2=3; Var3=3; Var4=3; output;
  Var1=.; Var2=4; Var3=4; Var4=3; output;
  Var1=.; Var2=5; Var3=5; Var4=.; output;
  Var1=.; Var2=.; Var3=6; Var4=.; output;
  Var1=.; Var2=.; Var3=7; Var4=.; output;
run;

proc transpose data=have out=inter;
  var var:;
run;

data inter (drop=i j);
  set inter;
  array col{7};
  do i=7 to 1 by -1;
    if col{i}=. then do;
      do j=i to 1 by -1;
        if col{j} ne . then do;
          col{i}=col{j};
          col{j}=.;
          leave;
        end;
      end;
    end;
  end;
run;

proc transpose data=inter out=want (drop=_name_);
  id _name_;
run;

[FreelanceReinh]

Here's a different solution which works on your example data, but it relies on the ascending sort order of non-missing values in the input dataset.

data have;
input Var1 Var2 Var3 Var4;
cards;
1 1 1 1
2 2 2 2
3 3 3 3
. 4 4 3
. 5 5 .
. . 6 .
. . 7 .
;

%let v1=var1;
%let v2=var2;
%let v3=var3;
%let v4=var4;

%macro shift(dsin=, dsout=, nvar=);
  %local i;
  %do i=1 %to &nvar;
    proc sort data=&dsin(keep=&&v&i) out=__&i;
    by &&v&i;
    run;
  %end;

  data &dsout;
  merge __1-__&nvar;
  run;

  proc datasets lib=work nolist;
  delete __1-__&nvar;
  quit;
%mend shift;

%shift(dsin=have, dsout=want, nvar=4);

proc print data=want;
run;

[aj69]

Thanks a bunch. I will try this out FreelanceReinhard

[FreelanceReinh · Accepted solution]

Please find below an improved version of my macro, adapted to the new information.

data have;
length hr_0-hr_23 $25;
input hr_0-hr_23;
cards;
2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
. 4 4 3 . 4 4 3 . 4 4 3 . 4 4 3 . 4 4 3 . 4 4 3
. 5 5 . . 5 5 . . 5 5 . . 5 5 . . 5 5 . . 5 5 .
. . 6 . . . 6 . . . 6 . . . 6 . . . 6 . . . 6 .
. . 7 . . . 7 . . . 7 . . . 7 . . . 7 . . . 7 .
;


%macro shift(dsin=, dsout=, var=);
  %local i;

  data __tmp;
  set &dsin;
  array v[24] &var.0-&var.23;
  array __m[24];
  do i=1 to 24;
    __m[i]=~missing(v[i]);
  end;
  run;

  %do i=1 %to 24;
    proc sort data=__tmp(keep=&var.%eval(&i-1) __m&i) out=__&i(drop=__:);
    by __m&i;
    run;
  %end;

  data &dsout;
  merge __1-__24;
  run;

  proc datasets lib=work nolist;
  delete __1-__24 __tmp;
  quit;
%mend shift;

%shift(dsin=have, dsout=want, var=hr_);

proc print data=want;
run;

Macro parameter VAR takes the common prefix of the 24 variables, hr_ in your example data.

(Edit: Replaced "have" by "&dsin" in SET statement.)

[aj69]

Much obliged FreelanceReinhard Can't thank you enough