topic Re: Move Data Up in SAS Programming

Move Data Up

ChelseyKate — Tue, 23 Feb 2016 22:46:36 GMT

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!

Re: Move Data Up

Reeza — Tue, 23 Feb 2016 22:49:38 GMT

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.

Re: Move Data Up

ChelseyKate — Tue, 23 Feb 2016 23:04:54 GMT

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.

Re: Move Data Up

Ksharp — Wed, 24 Feb 2016 02:38:43 GMT

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;

Re: Move Data Up

Ksharp — Wed, 24 Feb 2016 02:59:43 GMT

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;

Re: Move Data Up

ChelseyKate — Wed, 24 Feb 2016 13:47:38 GMT

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

Re: Move Data Up

Ksharp — Thu, 25 Feb 2016 00:58:45 GMT

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;

Re: Move Data Up

Ksharp — Thu, 25 Feb 2016 01:06:47 GMT

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;