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

by

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.

  • 1

    I’d recommend using a data step rather than fedsql here. Doing this in a single thread would be easiest.

    – 




  • I tried this initally, but I hit the data limit. Putting options casdatalimit=2000M; fixed it though

    – 

  • You could also save it to casuser. You’d use more memory but it won’t pull to disk. E.g. data casuser.want / single=yes; set caslib.have; run;

    – 




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
...

Leave a Comment