topic Re: Splitting a query on number of rows in SAS Programming

Splitting a query on number of rows

Holmes — Fri, 09 Dec 2022 21:02:01 GMT

Hello guys! (Sorry for my english, not a native)

I hope someone of you helps me...

I have a query in netezza that's disconnecting me because of time of query.

Is there anyway to make a looping (or something else) to get first 1 million rows of this query and next 1 million rows and then on?

Re: Splitting a query on number of rows

ballardw — Fri, 09 Dec 2022 22:57:41 GMT

You should include the code you are currently using for your request. That will help us have a better idea of where bottlenecks may occur.

You may find such things work better by creating subsets on the remote DBMS, if possible, and then transferring data to SAS.

Re: Splitting a query on number of rows

SK_11 — Thu, 15 Dec 2022 04:16:34 GMT

data TargetTable;
do Id=1 to 10;
output;
end;
run;

Proc sql;
Select count(*) as count into :Count from TargetTable
;Quit;
%put &count;

%let subsetsize=3;
%let tablecount=%sysfunc(ceil(%sysevalf(&count/&subsetsize)));
%put &tablecount;

%macro createtables;
%let fo=1;
%do i=1 %to &tablecount;
data newtable&i;
set TargetTable (obs = %sysevalf(&subsetsize+&fo-1) firstobs = &fo) ;
run;
%let fo=%sysevalf(&subsetsize+&fo);
%end;
%mend;

%createtables;

Output:

NewTable1	NewTable2	NewTable3	NewTable4
Id	Id	Id	Id
1	4	7	10
2	5	8
3	6	9

Re: Splitting a query on number of rows

ballardw — Thu, 15 Dec 2022 05:37:40 GMT

This doesn't show anything related to a connection to Netezza. The transfer of data from Netezza to SAS is likely the major bottleneck. So you should likely show where in your process are bringing the data to SAS from Netezza.