topic Re: Using Fetch function in SAS Programming

Using Fetch function

JasonNC — Tue, 04 Dec 2012 11:10:59 GMT

Can any one give me some examples of using fetch function in base sas .

For example

In one step i need to create a table with required values and at the same time i need to INSERT VALUES into another table

For example i have a data set STATE

DATA LSTATE MSTATE;

Instead of SET i need to use Fetch function and pass each observation and check the values.

In this step i need to output Lstate when the id ='NC' and i need to check Rep if Rep ne ' ' then i need to do

run;

Insert into mstate

select * from Tot where Rep=&Rep from STATE;

Re: Using Fetch function

Tom — Tue, 04 Dec 2012 12:51:40 GMT

I doubt you can do that in a single step.

I am not clear about what you want to do, but it looks like you want to generate LSTATE from STATE and also generate a list of REP values to use to pull information from TOT to insert into MSTATE. You should be able to generate LSTATE and the list of REP values in one step. Then you can use the list of REP values to insert records from TOT into MSTATE.

data lstate replist(keep=rep);

set state;

if state='NC' then output lstate ;

if rep ne ' ' then output replist;

run;

proc sql ;

insert into mstate select * from tot where rep in (select rep from replist);

quit;

Re: Using Fetch function

Haikuo — Tue, 04 Dec 2012 19:17:11 GMT

Like Tom suggested, in general you can't do it in one step. However, it can be cheated using Hash():

data _null_;

if _n_=1 then do;

if 0 then set mstate;

declare hash h1(dataset:’mstate (where=(state=’NC’))’, multidata:’y’);

h1.definekey(‘state’);

h1.definedata(all:’y’);

h1.definedone();

declare hash h2(dataset:’mstate (where=(not missing(rep)))’, multidata:’y’);

h2.definekey(‘rep’);

h2.definedata(all:’y’);

h2.definedone();

declare hash h3(dataset:’mstate’, multidata:’y’);

h3.definekey(‘rep’);

h3.definedata(all:’y’);

h3.definedone();

end;

rc=h1.output(dataset:’lstate’);

do until (last);

set tot end=last;

if h2.find()=0 then h3.add();

if last then h3.output(dateset:’mstate’);

end;

stop;

run;

Haikuo

Re: Using Fetch function

JasonNC — Wed, 05 Dec 2012 11:22:16 GMT

Hi,

The problem I am having is

In my case i have a data set with some thing about 10 million and i need to do a look up on DB2 table which contains about 600 million records based on iD and pull some information for that ID.

When i am doing subquery like this

Proc sql noprint;

select id,info

from db2.table

where id in (select id from sas data set)

The problem here is when this query goes to Db2 the query against d2 table is going the query other than subquery is sent to db2 and pulling the entire 600 million records and then executing the subquery as a result it is showing DB2 CLI cursor error.

Please let me know how to achieve this efficiently.

Re: Using Fetch function

Tom — Wed, 05 Dec 2012 12:31:07 GMT

You could see if you can get this libname option to work.

MULTI_DATASRC_OPT=in_clause

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0tcetvx1zpnayn1r8actrkjrd5o.htm

You might need to reformat the query as a join.

If the number of values is small enough then you could put them into a macro variable using SQL query.

For character variables:

select quote(trim(rep)) into :replist separated by ',' from replist ;

For numeric variable

select rep into :replist separated by ',' from replist;

Then you can use the list with an IN operator.

select * from db2.tot where rep in (&replist) ;