by I have some code that groups and summarizes data on a DB2 table and then counts
the number of rows in total set of summarized data. Step is coded as follows:
proc sql noprint;
connect to db2 (ssid=&ssid);
execute (set current sqlid=&boxp) by db2;
select kount6 into: kount6 from
(select count(*) as kount6 from connection to db2
(select fund_id_i
,sum(units_n)
from &db2auth..t32_box_l2_txn_MLY
where prtn_no_n=&l2_txn
and systm_d > &db2ldate
group by systm_d
,prtn_no_n
,prgrm_id_i
,run_no_n
,txn_type_id_i
,eff_date_d
,srs_x
,fund_id_i
,units_f
,class_x
,cntbn_type_x
,swtch_drctn_x
for fetch only))
;
%put &sqlxmsg;
disconnect from db2;
quit;
This works but is essentially a two step process: DB2 summarizes the data and
then passes the data across the connection to SAS where the number of rows
are counted.
Is it possible to do it all on the DB2 side i.e. summarize and count such that
only the total is passed back ? Is it also possible to group and count data
without having to provide a summary function ?
I have had a few stabs at this but am only able to get DB2 to return the number
of rows in the last group processed.
Many thanks.