12-04-2012 06:10 AM
Can any one give me some examples of using fetch function in base sas .
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
Insert into mstate
select * from Tot where Rep=&Rep from STATE;
12-04-2012 07:51 AM
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);
if state='NC' then output lstate ;
if rep ne ' ' then output replist;
proc sql ;
insert into mstate select * from tot where rep in (select rep from replist);
12-04-2012 02:17 PM
Like Tom suggested, in general you can't do it in one step. However, it can be cheated using Hash():
if _n_=1 then do;
if 0 then set mstate;
declare hash h1(dataset:’mstate (where=(state=’NC’))’, multidata:’y’);
declare hash h2(dataset:’mstate (where=(not missing(rep)))’, multidata:’y’);
declare hash h3(dataset:’mstate’, multidata:’y’);
do until (last);
set tot end=last;
if h2.find()=0 then h3.add();
if last then h3.output(dateset:’mstate’);
12-05-2012 06:22 AM
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;
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.
12-05-2012 07:31 AM
You could see if you can get this libname option to work.
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) ;