How do I divide a large table into multiple tables with a set number of rows using proc fedsql?

Question

I have a large table which I want to divide into multiple tables of 1 million rows each (otherwise it hits the download limit). How can I do this using proc fedsql (in SAS Viya)? If using proc sql, I would just use monotonic, but that doesn't appear to be an option for prod fedsql.

Answer 1

SQL has no built-in concept of row-number. If your data is can be ordered by a natural key, you can use a series of queries that use offet and limit clauses.

select * from myordereddata order by key offset      0 limit 1000000
select * from myordereddata order by key offset 100000 limit 1000000
...