topic Re: Split the table counting the row in SAS Programming

Split the table counting the row

SASdevAnneMarie — Tue, 04 Mar 2025 18:08:38 GMT

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 !

Re: Split the table counting the row

PaigeMiller — Tue, 04 Mar 2025 18:18:01 GMT

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?

Re: Split the table counting the row

Stu_SAS — Tue, 04 Mar 2025 18:31:39 GMT

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.

Re: Split the table counting the row

SASdevAnneMarie — Tue, 04 Mar 2025 20:29:41 GMT

Thank you, Stu_SAS!

Re: Split the table counting the row

data_null__ — Tue, 04 Mar 2025 22:52:34 GMT

@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);