Date:         Thu, 30 Jun 2011 10:29:10 -0700
Reply-To:     Fareeza Khurshed <fkhurshed@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Fareeza Khurshed <fkhurshed@GMAIL.COM>
Subject:      Re: SQL pass through create table in Oracle?
Comments: To: Ya Huang <ya.huang@AMYLIN.COM>
In-Reply-To:  <201106301723.p5UAlN0n024972@waikiki.cc.uga.edu>
Content-Type: text/plain; charset=us-ascii

Have you tried using into rather than create table in your code?

Ie.

Select *
Into &schema..test
From table1 ....

HTH,
Fareeza

On 2011-06-30, at 10:23 AM, Ya Huang <ya.huang@AMYLIN.COM> wrote:

> I can o this:
>
> proc sql;
>  connect to oracle(path=&URL user=&UID pass=&PSS);
>    select *
>    from connection to oracle
>    (
>     select *
>     from (select CASE_ID, CASE_NUM
>           from LSS_ESD_DATA_VIEW.lss_esd_rpt_case) a,
>          (select case_id, event_seq_num
>           from LSS_ESD_DATA_VIEW.lss_esd_rpt_event_assess) b
>      where a.case_id=b.case_id
>     );
> quit;
>
> But I wonder if this is too much of nesting.
>
> On Thu, 30 Jun 2011 13:16:31 -0400, Ya Huang <ya.huang@AMYLIN.COM> wrote:
>
>> Hi there,
>>
>> It seems that the only way I can get the data I want from Oracle fast
>> enough is to do as much as possible data manipulation in Oracle, so that
> the
>> result data is much smaller to get throught the network faster. But
>> sometimes, I can't get all joins done in one sql step, therefore I have
>> to have two pass through, then join the data locally in SAS. I wonder
>> if I can create temp tables (like I can create table in SAS WORK) in
> Oracle,
>> then join them over there in Oracle, then pass through the result data.
>>
>> I tried something like this:
>>
>> proc sql;
>> connect to oracle(path=&URL user=&UID pass=&PSS);
>>   select *
>>   from connection to oracle
>>   (create table &schema..junk1 as
>>    select CASE_ID, CASE_NUM
>>    from LSS_ESD_DATA_VIEW.lss_esd_rpt_case;
>>
>>    create table &schema..junk2 as
>>  select case_id, event_seq_num
>>    from LSS_ESD_DATA_VIEW.lss_esd_rpt_event_assess;
>>
>>    select *
>>    from &schema..junk1 a,
>>         &schema..junk2 b
>>     where a.case_id=b.case_id;
>>    );
>> quit;
>>
>>
>> But I got the message "ERROR: ORACLE prepare error: ORA-24333: zero
>> iteration count. ".
>>
>> Am I using the correct syntax?
>>
>> Thanks
>>
>> Ya