SAS, Sybase

SASPhile — Sat, 05 Jan 2013 16:47:55 GMT

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;

Re: SAS, Sybase

TomKari — Sat, 05 Jan 2013 17:33:48 GMT

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

topic SAS, Sybase in SAS Programming

SAS, Sybase

Re: SAS, Sybase