topic Re: proc sql in SAS Programming

proc sql

Pooja2 — Fri, 27 Sep 2019 14:04:40 GMT

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,

Re: proc sql

novinosrin — Fri, 27 Sep 2019 14:11:02 GMT

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';

Re: proc sql

r_behata — Fri, 27 Sep 2019 14:30:31 GMT

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

Re: proc sql

Pooja2 — Fri, 27 Sep 2019 14:32:35 GMT

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

Re: proc sql

PGStats — Fri, 27 Sep 2019 18:24:54 GMT

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.

Re: proc sql

sustagens — Mon, 30 Sep 2019 03:21:21 GMT

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

Re: proc sql

LinusH — Mon, 30 Sep 2019 13:53:02 GMT

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.