proc sql

[Pooja2]

I have a list of 2000 observations. I have to look for other details from a datawarehouse for these 2000 observations. I have tried two methods:

proc sql;

create table want as

select a.* /*selecting a total of 20 variables*/

from datawarehouse a

where a.var1 in (select distinct obs2000 from have);

quit;

this section is taking forever. I also tried following:

proc sql noprint;

select distinct obs2000 from have into :obs;

quit;

proc sql;

create table want as

select a.* /*selecting a total of 20 variables*/

from datawarehouse a

where a.var1 in (&obs.);

quit;

this section is giving me an error "length is more than 256 characters long"

My question is what are other ways to solve a query like these.

Thanks,

[novinosrin]

Hi @Pooja2  Try Hash as an alternative

data want ;
if _n_=1 then do;
   if 0 then set have;/*load distinct values of obs2000 in hash table*/
   dcl hash H (dataset:'have') ;
   h.definekey  ("obs2000") ;
   h.definedata ("obs2000") ;
   h.definedone () ;
end;
set datawarehouse ;
if h.check(key:var1)=0;/*check if exists*/
/*Do whatever logic you want*/
run';

[r_behata]

Apparently your Data warehouse is a relational DB. have you tried the SQL pass Through option ?

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

[Pooja2]

no. I have not. But thanks for the link.

[PGStats]

You certainly got errors in the log. Assuming obs2000 are strings, the correct syntax is:

proc sql noprint;
select distinct quote(trim(obs2000)) into :list separated by "," from have;
quit;

proc sql;
create table want as
select *
from datawarehouse
where var1 in (&list.);
quit;

if obs2000 are numbers, remove the calls to functions quote and trim.

[sustagens]

proc sql;
	create table want as (
	select datawarehouse.* 
	from datawarehouse 
	   ,(select distinct obs2000 from have)
	where datawarehouse.var1 = have.obs2000
	);
quit;

[LinusH]

Try to exploit the DBKEY option. It does this string for you OOTB.
Another option is to upload your small data set to a RDBMS temporary table, and then the join will be performed there.
Hash is probably not a good option since it will upload the whole RDBMS table from the database, and you only wish a tiny slice of it.