Hello all;
There is a very large external flat file named external, I want to use it in hash join.how to use infile statement to do it?
Here is test code:
data external; /*I don't want to create this data set here,the data is too large,I want use infile in hash join step directly*/
infile cards;
input ptnum $ 1-3 @5 date date9. event $ 15-35;
format date date9.;
cards;
ABC 16NOV2009 Nausea
DEF 16NOV2009 Heartburn
DEF 16NOV2009 Acid Indigestion
DEF 18NOV2009 Nausea
GHI 17NOV2009 Fever
GHI 18NOV2009 Fever
MNO 17NOV2009 Fever
;
run;
data cm;
infile cards;
input ptnum $ 1-3 @5 date date9. medication $ 15-35;
format date date9.;
cards;
ABC 16NOV2009 Dopamine
DEF 16NOV2009 Antacid
DEF 16NOV2009 Sodium bicarbonate
aaa 18NOV2009 Dopamine
bbb 18NOV2009 Asprin
ccc 19NOV2009 Asprin
ddd 17NOV2009 Asprin
;
run;
Data ae_rspndt;
If _n_ = 1 then do;
if 0 then set cm ;
declare hash cm(dataset: "work.cm") ;
rc=cm.defineKey("ptnum", 'date');
rc=cm.defineData(ALL: 'YES');
rc=cm.defineDone() ;
end;
do until (eof);
set external end=eof;/*In this step, I want to change set external to infile statement*/
rc = cm.find();
if rc = 0 then do;
output ;
end;
end;
stop;
Run;
I want change "set external" to directly use infile statement like:
Data ae_rspndt2;
If _n_ = 1 then do;
if 0 then set cm ;
*declare hash cm(dataset: "work.cm",hashexp:16,multidata:"Yes") ;
declare hash cm(dataset: "work.cm") ;
rc=cm.defineKey("ptnum", 'date');
rc=cm.defineData(ALL: 'YES');
rc=cm.defineDone() ;
end;
do until (eof);
infile "An--external--file" end=eof;
input ptnum $ 1-3 @5 date date9. event $ 15-35;
rc = cm.find();
if rc = 0 then do;
output ;
end;
end;
stop;
Run;
But itnot works, please help.
Thanks!
I have newly added an efficiency Note at the bottom of the post.
This may help you.
Data want;
filename myext "C:\Users\K\ALLSAS\Prod\TEST.txt";
If _n_ = 1 then do;
if 0 then set cm ;
declare hash cm(dataset: "work.cm") ;
cm.defineKey("ptnum", 'date');
cm.defineData(ALL: 'YES');
cm.defineDone() ;
end;
do until(eof);
infile myext end = eof ;
input @1 ptnum $ 1-3 @5 date date9. @15 event &$ 15-35;
format date date9.;
if cm.find() = 0 then output;
end;
if eof then stop;
run;
proc print data = want;
run;
You place your text file in some external directory. The need of symbol & is a must.
I think the use of rc is not must in this application. The return code of find() can be checked without rc as done here. It is my style.
The Efficiency Note:
The variables that are KEY part of the hash table are common to both data set. The only variable that is needed to go the data part of the hash table is "Medication". Replacing
cm.defineData(ALL: 'YES');
by
cm.definedata('medication');
will save 16 bytes of memory for each item of hash table.
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.