topic Re: Subquery in SAS Procedures

Subquery

Miracle — Fri, 05 Feb 2021 09:57:56 GMT

Dear All,

How do we write the proc sql to retrieve only the observation rows when the same ID and the same date are present in both the data please?

Thanking you all in advance.

Re: Subquery

PeterClemmensen — Fri, 05 Feb 2021 10:08:40 GMT

What do you mean by "both the data" ?

Please be more specific and show us your data.

Re: Subquery

Kurt_Bremser — Fri, 05 Feb 2021 10:10:40 GMT

proc sql;
create table want as
  select t1.*
  from t1
  where id in (select distinct id from t2)
;
quit;

Replace table and column names as needed

Depending on dataset size, a DATA step with hash approach might perform better.

Re: Subquery

PGStats — Fri, 05 Feb 2021 16:46:52 GMT

You can't use the IN operator for subsetting on two variables, but you can use the EXISTS operator:

proc sql;
create table want as
  select *
  from t1 as a
  where exists (select * from t2 where id=a.id and date=a.date);
quit;

Re: Subquery

Miracle — Sun, 07 Feb 2021 14:35:51 GMT

Thanks @PeterClemmensen , @Kurt_Bremser and @PGStats for your response.
I tried the proc sql as you advised but the code never stop running even for an entire day.
I wonder if it is due to the huge datasets i.e 10000k rows of data.
What other ways would you suggest please?
I am completely new to hash object so I don't understand how to write the syntax.
Thanking you in advance.

Re: Subquery

Tom — Sun, 07 Feb 2021 18:57:36 GMT

@Miracle wrote:

Dear All,

How do we write the proc sql to retrieve only the observation rows when the same ID and the same date are present in both the data please?

Thanking you all in advance.

Why would you use SQL for such a simple request?

data want ;
  merge dataset1 (in=in1)  dataset2(in=in2);
  by id date;
  if in1 and in2;
run;

Re: Subquery

Kurt_Bremser — Mon, 08 Feb 2021 17:27:15 GMT

Then a hash approach should be tried.

Which would look like this:

data want;
set t1;
if _n_ = 1
then do;
  declare hash t2 (dataset:("t2");
  t2.definekey("id","date");
  t2.definedone();
end;
if t2.check() = 0;
run;

This will work as long as the two variables * observations from t2 fit into memory.