Split the table counting the row

[SASdevAnneMarie]

Hello Experts,

I have the sas data with 3 727 000 rows, I would like to split this table on 4 tables, but I dont know how apply the firstobs and lastobs correctly. Could you help me please ?

Thank you !

[PaigeMiller]

Splitting a table like you describe is not a good idea, in most situations. So the easiest thing to do is to NOT split it.

Can you explain why you want to split this table? What can you do with the split table that you can't do without splitting the table?

[Stu_SAS · Accepted solution]

Hey @SASdevAnneMarie! I've got some old code that does this:

%macro split_data(data=, splits=);
   
   %let dsid  = %sysfunc(open(&data));
   %let n     = %sysfunc(attrn(&dsid, nlobs));
   %let rc    = %sysfunc(close(&dsid));

   %put Total obs: &n;
   %put -------------------------------;

   %do s = 1 %to &splits;
       %let firstobs = %sysevalf(&n-(&n/&splits)*(&splits-&s+1)+1, floor);
       %let obs      = %sysevalf(&n-(&n/&splits)*(&splits-&s), floor);
       
       %put split:    &s;
       %put firstobs: &firstobs;
       %put obs:      &obs;
       %put total:    %eval(&obs-&firstobs+1);
       %put -------------------------------;
    %end;
%mend;

For example:

%split_data(data=sashelp.cars, splits=3);

Total obs: 428
-------------------------------
split:    1
firstobs: 1
obs:      142
total:    142
-------------------------------
split:    2
firstobs: 143
obs:      285
total:    143
-------------------------------
split:    3
firstobs: 286
obs:      428
total:    143
-------------------------------

You can modify this to save each split point to a macro variable. There are probably a dozen other ways to do this, but this is one that I've had for many years to help me find split points. I mostly used it for splitting data up to work in parallel RSUBMIT sessions, but occasionally have used it to send data to people as chunked up CSV files since they requested it that way.

[SASdevAnneMarie]

Thank you, Stu_SAS!

[data_null__]

@SASdevAnneMarie you could add a parameter to @Stu_SAS macro XMACRO to execute a macro within the %DO;

%macro split_data(data=, splits=, xmacro=xmacro);
   
   %let dsid  = %sysfunc(open(&data));
   %let n     = %sysfunc(attrn(&dsid, nlobs));
   %let rc    = %sysfunc(close(&dsid));

   %put Total obs: &n;
   %put -------------------------------;

   %do s = 1 %to &splits;
       %let firstobs = %sysevalf(&n-(&n/&splits)*(&splits-&s+1)+1, floor);
       %let obs      = %sysevalf(&n-(&n/&splits)*(&splits-&s), floor);
       %&xmacro;
       %put split:    &s;
       %put firstobs: &firstobs;
       %put obs:      &obs;
       %put total:    %eval(&obs-&firstobs+1);
       %put -------------------------------;
    %end;
%mend;
 

%macro xmacro;
   data cars_split&s(label="&data &firstobs:&obs");
      set &data(firstobs=&firstobs obs=&obs);
      run;
   %mend xmacro;

%split_data(data=sashelp.cars, splits=3);