BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gnanau
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

Form 1:

DECLARE PACKAGE HASH variable ( );

Form 2:

DECLARE PACKAGE HASH variable (hashexp, {'datasource' | '\{sql-text\}'},
'ordered', 'duplicate ', 'suminc', 'multidata');

Form 3:

DECLARE PACKAGE HASH variable ( \[keys\], \[data\], [hashexp,
{'datasource' | '\{sql-text\}'}, 'ordered', 'duplicate ', 'suminc', 'multidata']
);

Form 4:

DECLARE PACKAGE HASH variable ( \[keys\], [hashexp, {'datasource' | '\{sql-text\}'},
'ordered', 'duplicate ', 'suminc', 'multidata']
);

View solution in original post

7 REPLIES 7
FriedEgg
SAS Employee

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;

FriedEgg
SAS Employee

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

Form 1:

DECLARE PACKAGE HASH variable ( );

Form 2:

DECLARE PACKAGE HASH variable (hashexp, {'datasource' | '\{sql-text\}'},
'ordered', 'duplicate ', 'suminc', 'multidata');

Form 3:

DECLARE PACKAGE HASH variable ( \[keys\], \[data\], [hashexp,
{'datasource' | '\{sql-text\}'}, 'ordered', 'duplicate ', 'suminc', 'multidata']
);

Form 4:

DECLARE PACKAGE HASH variable ( \[keys\], [hashexp, {'datasource' | '\{sql-text\}'},
'ordered', 'duplicate ', 'suminc', 'multidata']
);

gnanau
Calcite | Level 5

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...


FriedEgg
SAS Employee

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.

gnanau
Calcite | Level 5

Thanks.

ScottBass
Rhodochrosite | Level 12

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?


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
ScottBass
Rhodochrosite | Level 12

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!


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 3082 views
  • 2 likes
  • 3 in conversation