Move Data Up

[ChelseyKate]

Good evening everyone! I am hoping someone can help me out. I need to get my data in a new format so that I can analyze it a certain way. Currently the data that I need to move up the specific cells when they are missing. The trick to this is I have hundreds of variables, and the number of variables need to change every month so I need it to be dynamic. I have been messing around with arrays and do loops, using macro variables, but can't get it. The only thing I can think of is to create a dataset with each variable then merge it back together but I need to automate it.

Here is what I have:

And I need it to look like this:

Thank you in advance!

[Reeza]

Can you back your process a step, how are you generating that file? Change that process...If you have the code for it, showing that would be helpful as would the original data format.

[ChelseyKate]

The format of the data before this is multiple rows per record of amount of dollars recovered by date. So I used Proc SQL to sum the recoveries up by month and then divide it by total loss to get percentage. So it's formatted this way because it's grouped by month.

[Ksharp]

data have;
input a b c d;
cards;
1 . . .
2 1 . .
3 2 1 .
4 3 2 1
;
run;
data _null_;
 set sashelp.vcolumn(where=(libname='WORK' and memname='HAVE')) end=last;
 if _n_ eq 1 then call execute('data want;merge ');
 call execute(catt('have(keep=',name,' where=(',name,' is not missing))'));
 if last then call execute(';run;');
run;

[ChelseyKate]

Thanks! This didn't seem to work and is running through every dataset I have in every library....

[Ksharp]

What do you mean it didn't work out ? Here is the code go through all the tables in WORK library.

data have;
input a b c d;
cards;
1 . . .
2 1 . .
3 2 1 .
4 3 2 1
;
run;
data have1;
input a b c d;
cards;
1 . . .
2 1 . .
3 2 1 .
5 3 2 1
;
run;
data _null_;
 set sashelp.vcolumn(where=(libname='WORK') keep=memname libname name) end=last;
 by memname;
 if first.memname then call execute(catt('data new_',memname,';merge '));
 call execute(catt(memname,'(keep=',name,' where=(',name,' is not missing))'));
 if last.memname then call execute(';run;');
run;

[Ksharp]

Here is IML code for going through all the tables in WORK library . much faster than data step.

data have;
input a b c d;
cards;
1 . . .
2 1 . .
3 2 1 .
4 3 2 1
;
run;
data have1;
input a b c d;
cards;
1 . . .
2 1 . .
3 2 1 .
5 3 2 1
;
run;

proc iml;
memname=datasets('WORK');
new_memname=cats('new_',memname);
do j=1 to nrow(memname);
 use (memname[j]);
 read all var _all_ into x[c=vname];
 close;
 
 y=j(nrow(x),ncol(x),.);
 do i=1 to ncol(x);
  idx_x=loc(x[,i]^=.); 
  idx_y=1:ncol(idx_x);
  y[idx_y,i]=x[idx_x,i];
 end;

create (new_memname[j])from y[c=vname];
append from y;
close;
end;
quit;

[Ksharp]

Here is IML code. If you have lots of variables, IML would save you lots of time.

data have;
input a b c d;
cards;
1 . . .
2 1 . .
3 2 1 .
4 3 2 1
;
run;
proc iml;
use have;
read all var _all_ into x[c=vname];
close;
y=j(nrow(x),ncol(x),.);
do i=1 to ncol(x);
 idx_x=loc(x[,i]^=.); 
 idx_y=1:ncol(idx_x);
 y[idx_y,i]=x[idx_x,i];
end;
create want from y[c=vname];
append from y;
close;
quit;