BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
3 REPLIES 3
Doc_Duke
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 3 replies
  • 866 views
  • 0 likes
  • 3 in conversation