topic Re: Oracle cursor in SAS Programming

Oracle cursor

mrscher22 — Thu, 24 Apr 2008 19:03:08 GMT

I have a cursor that I would like to execute via proc sql in base SAS. Is this possible?

here is my code:

declare
v_cust_id int;
v_sales_rep_id int;

cursor orphan_cursor is
select cust_id
from DTF_SALES_ITS_recur_5
order by cust_id desc;

cursor reps_cursor is
select sales_rep_id
from DTF_SALES_ITS_recur_reps2
order by rand_number;

begin
open reps_cursor;
for orph_val in orphan_cursor
loop
fetch reps_cursor into v_sales_rep_id;
if reps_cursor%notfound then
close reps_cursor;
open reps_cursor;
fetch reps_cursor into v_sales_rep_id;
end if;
v_cust_id := orph_val.cust_id;
INSERT into univ_assign_orphans VALUES (v_cust_id, v_sales_rep_id);
end loop;
close reps_cursor;
end;

Re: Oracle cursor

deleted_user — Thu, 24 Apr 2008 19:33:06 GMT

1) Wouldn't it make more sense to either run this from SQL Plus? or as a stored procedure?

2) You may be able to use the pass-through facility and execute statement.

3) Does your code have some typo's, because it doesn't look like it would work as intended to me.

4) It seems to me that if you want to use SAS and determine if there are orphaned customer id's and sales_rep id's, you could use proc sql directly with some set relations:
[pre]
proc sql;
select something from somewhere
except corr
select something from somewhereelse;
quit;
[/pre]

Re: Oracle cursor

deleted_user — Fri, 25 Apr 2008 15:31:45 GMT

Thanks for the reply, Chuck.

I seems like your suggestion of using a stored proc is the right answer to my problem. The semi-colon in the code is the culprit even though it is in the passthrough statement, SAS parses the statement before passing it over to the dbms.