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!
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.
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;
Thanks! This didn't seem to work and is running through every dataset I have in every library....
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: