topic Re: Make data step working like SQL right join in SAS Health and Life Sciences

Make data step working like SQL right join

deleted_user — Wed, 06 Feb 2008 14:10:48 GMT

Hi gyes
I need to make this code

data test;
set table1;
declare hash h0 (dataset: "table1",hashexp:10);
h0.DefineKey ("Field1");
h0.DefineData ("Field2");

h0.DefineDone();

do until (eof);
set table2 end=eof;
rc=h0.find();
if rc = 0 then output;
end;
stop;
run;

worked like this SQL-query:

create table test as (select * from table2 t2 right join table1 t1 on t1.Field1 = t2.Field1);

It almost the same, but sql outputs records even they are not in table2 (cause of right join) and data step doesn't do that.
Can somebody please explain how can I force it to output null record?

Re: Make data step working like SQL right join

Doc_Duke — Wed, 06 Feb 2008 15:12:06 GMT

What you want is a MERGE, not a SET.

* untested code;
PROC SORT DATA=table1; BY field1; RUN;
PROC SORT DATA=table2; BY field2; RUN;

DATA test;
MERGE table2 (in=int2)
table1 (in=in1);
BY field1;
IF in1;
RUN;

You may get slightly different results if there are repeated values of field1 (particularly if there are matching repeats in both datasets).

Doc Muhlbaier
Duke

Re: Make data step working like SQL right join

deleted_user — Wed, 06 Feb 2008 15:34:58 GMT

Thank you for this code, but I need to use hash objects, because there are very large tables and i have no idea how can I do this using merge.

Re: Make data step working like SQL right join

Cynthia_sas — Wed, 06 Feb 2008 16:13:02 GMT

Hi:
If you have an immediate need, I think your best bet for a response is to contact SAS Tech Support.

The problem I'm having with your request is your insistence on using Hash tables. Even if you have a large file, a hash table might not be the best way to get a count and a sum, even with the need to do the join. On the other hand, a hash table might be the right approach. Only Tech Support can tell you for sure. Like Doc, I would use Proc Means or Proc Tabulate, both of which are highly efficient at summarizing and/or counting.

However, since you really seem to want a hash table approach, then Tech Support would be the right people to help you. They can find out ALL the specifics of your big tables and help you with a solution in a timely manner.

cynthia