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,
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';
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
no. I have not. But thanks for the link.
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.
proc sql;
create table want as (
select datawarehouse.*
from datawarehouse
,(select distinct obs2000 from have)
where datawarehouse.var1 = have.obs2000
);
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.