topic Re: SAS program stuck forever while executing proc sql insert in SAS Programming

SAS program stuck forever while executing proc sql insert

leen — Mon, 04 Dec 2023 23:40:49 GMT

Hi, my sas is stuck everytime I try to run this code:

proc sql;
    insert into myora.Customer_t
    set customerid = 100,
        customername = "Cool Future",
        customeraddress = "123 Lake Side Drive",
        customercity = "Ithaca",
        customerstate = "NY",
        customerpostalcode = "14850";
quit;

But I can connect to myora and select as normal (as shown below:)

1    libname myora oracle user="xxx" password=XXXXXXXXXX path="xe";
NOTE: Libref MYORA was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: xe
1  !                                                                  run;
2    libname orasas "D:\hw5";
NOTE: Libref ORASAS was successfully assigned as follows:
      Engine:        V9
      Physical Name: D:\hw5

3    title2 "Output2";
4    proc sql;
5    select * from
6    (select * from myora.customer_t where customerstate="NY" or customerstate="PA")
7    order by customerstate, customername;
NOTE: Writing HTML Body file: sashtml.htm
8    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.25 seconds
      cpu time            0.09 seconds

This is what it looks like when I submit those code:

I really don't know why it is stuck every time just for this insert statement. I tried to wait for it. But it just won't finish executing.

There is no log output, I can't even scroll the log window, or anywhere else.

I can insert this row as normal in SQL developer:

INSERT INTO Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850');

(Log: "1 row inserted.")

Re: SAS program stuck forever while executing proc sql insert

ballardw — Mon, 04 Dec 2023 23:42:59 GMT

Are you sure that the credentials you log into Oracle with have permission to write to that particular table?

Also, what constraints are on the customer_t table? Perhaps what you attempt to insert violates one or more constraints such as missing a variable.

And in the possibly lower probability of causing problems I see you reference "Customer_t" in the first SQL and "customer_t" in the second. I don't use Oracle so don't know how case sensitive names for table may be. So you may want to ask the Oracle administrators.

Re: SAS program stuck forever while executing proc sql insert

leen — Mon, 04 Dec 2023 23:49:54 GMT

Oracle sql plus is not case sensitive. The credentials are the same as those in sql developer. But I can insert as normal there, which means there shouldn’t be any permission/modifying rule issue. even though there is one i think I should get an error instead of running forever.

Re: SAS program stuck forever while executing proc sql insert

Tom — Tue, 05 Dec 2023 00:06:36 GMT

Why are you using two totally different syntaxes for the INSERT statement?

What happens if you use the second syntax in SAS?

libname myora oracle ...... ;
proc sql;
INSERT INTO myora.Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850')
;
run;

Re: SAS program stuck forever while executing proc sql insert

Patrick — Tue, 05 Dec 2023 00:23:03 GMT

Just as a thought: In the screenshot you share you're issuing a DELETE and an INSERT within the same Proc SQL. It could be - just a theory - that the DELETE doesn't get committed and though the INSERT is waiting.

To test the theory: Issue the DELETE and INSERT using two Proc SQL to ensure that the DELETE gets commited prior to the INSERT.

Re: SAS program stuck forever while executing proc sql insert

leen — Tue, 05 Dec 2023 03:23:54 GMT

Thank you but No that’s what I added after I kept having this problem.

Re: SAS program stuck forever while executing proc sql insert

SASKiwi — Tue, 05 Dec 2023 03:52:55 GMT

Try SQL Passthru. That's exactly like using SQL Developer:

proc sql;
  connect using myora;
  execute (
INSERT INTO Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850')
) by myora;
run;

Re: SAS program stuck forever while executing proc sql insert

Sajid01 — Tue, 05 Dec 2023 14:46:57 GMT

Hello @leen .
Reading this post along with your other post (https://communities.sas.com/t5/SAS-Programming/SAS-takes-so-long-to-initiate-print-even-very-small-dataset/m-p/905810#M357718 ) and the fact that you have used path="XE", gives me the impression that you have PC SAS and Oracle express installed on your PC and that your SAS initialization and often running small tasks is slow.
Under these circumstances creating a libname statement implies bring your entire Oracle table into SAS at the backend. That may cause the execution of the process to be slow.
Rather than dwell on the mechanics of the process, I would recommend that you try SQL pass through as suggested by @SASKiwi

Re: SAS program stuck forever while executing proc sql insert

leen — Wed, 06 Dec 2023 07:10:07 GMT

I can use sql pass through but it's a homework assignment so i am required to use libref. Anyway I mis-clicked on the accept button and I might find solution by myself. Thank you.

Re: SAS program stuck forever while executing proc sql insert

SASKiwi — Wed, 06 Dec 2023 19:08:44 GMT

To be pedantic, you are using the LIBREF as the definition for the Passthru connection...🙂