Hi All,
The following code creates the table #movecluster only if a.cluster_cnt + b.cluster_cnt <= 1000.
How do we create another table say #thresh when a.cluster_cnt + b.cluster_cnt >1000 and then write to SAS dataset.
We need to_id,from_id,a..cluster_cnt,b..cluster_cnt in the SAS dataset.
There are 400 mil records being processed.
%local rowcnt i;
%do i = 1 %to 90;
%let rowcnt = 0;
execute (
select to_id, min(from_id) from_id
into #movecluster
from (
select min(a.to_id) as to_id, a.from_id
from (
select a.cust_cluster_id to_id
,b.cust_cluster_id from_id
from proc_custs_clust_work a join vise_share a1 on a.cust_xref_id = a1.cust_xref_id
join vise_share b1 on b1.cluster_id = a1.cluster_id
join proc_custs_clust_work b on b.cust_xref_id = b1.cust_xref_id
and a.cluster_cnt + b.cluster_cnt <= 1000
where a.cust_cluster_id < b.cust_cluster_id
group by a.cust_cluster_id, b.cust_cluster_id
) a
group by a.from_id
) b
group by to_id
) by sybase;
I may be misunderstanding something.
It looks to me like you can just reuse the same code, changing where...<=1000 to where...>1000, and changing your into dataset from #movecluster to #thresh. Then do a "create table work.want as select * from #thresh".
I have the feeling that for someone with your SQL coding skills to post a question like this, there is complexity that I don't see. Please expand!
Tom
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.