BookmarkSubscribeRSS Feed
ChelseyKate
Calcite | Level 5

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!

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5
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
Super User
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
Calcite | Level 5

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

Ksharp
Super User

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
Super User

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1000 views
  • 1 like
  • 3 in conversation