DATA Step, Macro, Functions and more

Move Data Up

Reply
New Contributor
Posts: 3

Move Data Up

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:

 

Capture.PNG

 

And I need it to look like this:

Capture1.PNG

 

Thank you in advance!

Super User
Posts: 19,822

Re: Move Data Up

Posted in reply to ChelseyKate

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.

New Contributor
Posts: 3

Re: Move Data Up

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.
Super User
Posts: 10,041

Re: Move Data Up

Posted in reply to ChelseyKate
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;
New Contributor
Posts: 3

Re: Move Data Up

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

Super User
Posts: 10,041

Re: Move Data Up

Posted in reply to ChelseyKate

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;
Super User
Posts: 10,041

Re: Move Data Up

Posted in reply to ChelseyKate

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;
Super User
Posts: 10,041

Re: Move Data Up

Posted in reply to ChelseyKate

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;
Ask a Question
Discussion stats
  • 7 replies
  • 304 views
  • 1 like
  • 3 in conversation