topic Re: How do I pull N rows from a database at a time ? in SAS Programming

How do I pull N rows from a database at a time ?

KatWinSASland — Sun, 07 Apr 2024 17:58:13 GMT

I recently started using an AWS credential to access data in a cloud database. Currently we are dealing with a limitation where we get timed out after 1 hour. (Not really what I need help with, but just to give some context why I am approaching the issue this way). This only allows me to pull about 1 million or so records at a time if I do a limit 1000000 statement at the end of the query. I don't have other fields that would make good filters to split the dataset up. Is there a way to pull the first million in one query, then make sure I am pulling the next million, so on and so forth. I can see how many records there are in each monthly table, so if I can do these pulls sequentially I should be able to pull all the records separately to address the credential issue.

Re: How do I pull N rows from a database at a time ?

SASKiwi — Sun, 07 Apr 2024 21:02:13 GMT

Yours is the second recent post on this topic. Here is the other one that may be of some help: https://communities.sas.com/t5/SAS-Programming/How-to-Stop-amp-Restart-Query-Every-30-min/m-p/922572#M363274

The obvious question is why can't the time out limit be changed? This could be the result of a firewall rule for example. I suggest you check with your SAS administrator before spending a lot of time working around this limitation - better to cure the disease rather than deal with the symptom.

Re: How do I pull N rows from a database at a time ?

Kurt_Bremser — Mon, 08 Apr 2024 08:01:51 GMT

Using FIRSTOBS= and OBS= dataset options:

%macro get_chunk(n);
data chunk&n.;
set source.table (firstobs=%eval(&n. * 1000000 + 1) obs=%eval((&n. + 1) * 1000000));
run;
%mend;

%get_chunk(0)
%get_chunk(1)
...

You need to add code that sets the correct OBS= value for the last chunk.