I am looking for an example on how to use a hash and hash iterator to iterate through a data set inside a DS2 data program. As practical application I would like to build the Cartesian product of two tables.
Each of the tables is a list of ID and Value and I want in face to end up with a data set containing all possible pairs of these 2 sets of Value.
I tried something like below but is not working. It looks like rc=hi.first(); returns an error code rc=4 but I cannot find anywhere what that means.
proc ds2;
data one (overwrite=YES);
method run();
set post.workPC (RENAME=(Value=Value1 TblID=TblID1));
end;
enddata;
run;
data two (overwrite=YES);
method run();
set post.workPC (RENAME=(Value=Value2 TblID=TblID2));
if TblID2 gt 1 then output;
end;
enddata;
run;
data prodcart (overwrite=Yes);
declare double TblID2 rc;
declare char(10) Value2;
declare package hash h ('two');
declare package hiter hi('h');
method init();
rc = h.defineKey('TblID2');
rc = h.defineData('Value2');
rc = h.defineDone();
end;
method run();
set one;
rc=hi.first();
put TblID2 Value2 TblID1 Value1;
end;
run;
quit;
your specific issue is that you are not declaring the hash with a valid syntax (you are missing a definition for hashexp), apparently this does not cause any kind of error to be produced, you just get a empty hash.
SAS(R) 9.4 DS2 Language Reference, Fourth Edition
data class;
set sashelp.class;
run;
data altclass;
set sashelp.class;
run;
proc ds2;
data foo (overwrite=yes);
dcl nchar(32) altname;
dcl package hash h(8, '{SELECT name as altname FROM altclass}');
dcl package hiter hi('h');
method init();
dcl double rc;
rc = h.defineKey('altname');
rc = h.defineData('altname');
rc = h.defineDone();
end;
method run();
dcl double rc;
set class;
rc = hi.first();
do while(rc=0);
output;
rc = hi.next();
end;
end;
enddata;
run;
quit;
your specific issue is that you are not declaring the hash with a valid syntax (you are missing a definition for hashexp), apparently this does not cause any kind of error to be produced, you just get a empty hash.
SAS(R) 9.4 DS2 Language Reference, Fourth Edition
Yes, you are right, FriedEgg !
Thank you very much...
Do you know if I can find anywhere what different values for the return code mean? In my case I was getting rc=4 but I cannot seem to find anywhere what these individual codes are...
The return codes are not documented, to my knowledge. An appropriate error message should be displayed if you exclude the rc=, if it behaves like the hash object in non-ds2 data step. For the `first` method basic meaning is: RC=0 = success, RC ≠ 0 = failure.
Thanks.
I tried the tiniest of variations of this, as follows:
data class;
set sashelp.class;
run;
data altclass;
set sashelp.class;
run;
proc ds2;
data foo (overwrite=yes);
dcl char(32) altname;
dcl package hash h(8, '{SELECT name as altname,age,sex FROM altclass}');
dcl package hiter hi('h');
method init();
dcl double rc;
rc = h.defineKey('altname');
rc = h.defineData('altname');
rc = h.defineDone();
end;
method run();
dcl double rc;
set class;
rc = hi.first();
do while(rc=0);
output;
rc = hi.next();
end;
end;
enddata;
run;
quit;
data &syslast;
set &syslast;
run;
data foo;
set foo;
run;
That works, although it's "irritating" that proc ds2 does not properly set &syslast.
data fact (keep=stock where=(stock='IBM'));
set sashelp.stocks;
run;
data lookup;
set sashelp.stocks;
run;
* check the sql - it works ;
proc sql;
SELECT stock,open,close FROM lookup;
quit;
proc ds2;
data foo (overwrite=yes);
dcl char(9) stock;
dcl double open close;
dcl package hash h(8, '{SELECT stock,open,close FROM lookup}'); * so why does this fail??? ;
dcl package hiter hi('h');
method init();
dcl double rc;
rc = h.defineKey('stock');
rc = h.defineData('open');
rc = h.defineData('close');
rc = h.defineDone();
end;
method run();
dcl double rc;
set fact;
rc = hi.first();
do while(rc=0);
output;
rc = hi.next();
end;
end;
enddata;
run;
quit;
data &syslast;
set &syslast;
run;
data foo;
set foo;
run;
But this fails:
ERROR: Syntax error at or near "open"
ERROR: Unable to prepare statement for hash data source {SELECT stock,open,close FROM lookup}.
ERROR: Error reported by DS2 package d2hash:
ERROR: Hash data source load failed.
ERROR: Fatal run-time error.
ERROR: General error
Can anyone translate this error message?
Ok, this works:
proc ds2;
data foo (overwrite=yes);
dcl char(9) stock;
dcl double open close;
dcl package hash h(8, '{SELECT stock,"open","close" FROM lookup}'); * so why does this fail??? ;
dcl package hiter hi('h');
method init();
dcl double rc;
rc = h.defineKey('stock');
rc = h.defineData('open');
rc = h.defineData('close');
rc = h.defineDone();
end;
method run();
dcl double rc;
set fact;
rc = hi.first();
do while(rc=0);
output;
rc = hi.next();
end;
end;
enddata;
run;
quit;
"open" and "close" are not listed as reserved words in Ch 21 "DS2 Reserved Words" of the DS2 Language Reference.
However, they are listed as reserved words in the FedSQL documentation: http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm#p0x0k1rlx0l7e4n1...
A link to that documentation from Ch 21 would be useful.
SAS R&D - would it be that hard to say something like "open is a reserved word" in the error message??? Clear and consise error messages make your customers' and Tech Support's life a lot easier!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.