topic Re: Dynamic hash column definition using existing dataset in PROC DS2 in SAS Procedures

Dynamic hash column definition using existing dataset in PROC DS2

ScottBass — Tue, 22 Mar 2016 05:34:01 GMT

Hi,

This works:

data class class_cols;
   set sashelp.class;
   output class;
run;

proc ds2;
   data test(overwrite=yes);
      dcl package hash h();
      dcl double num;
      method init();
         if 0 then set class_cols (locktable=share);
         h.definekey('name');
         h.definekey('sex');
         h.definedata('age');
         h.definedata('height');
         h.definedata('weight');
         h.dataset('{select * from class where sex=''F''}');
         h.definedone();
         num=h.num_items;
         put num=;
      end;
   enddata;
   run;
quit;
data test;set test;run;  * to get EG to display the results from DS2 ;

But this fails:

data class class_cols;
   set sashelp.class;
   output class;
run;

proc ds2;
   data test(overwrite=yes);
      dcl package hash h();
      dcl double num;
      method init();
         if 0 then set class (locktable=share);
         h.definekey('name');
         h.definekey('sex');
         h.definedata('age');
         h.definedata('height');
         h.definedata('weight');
         h.dataset('{select * from class where sex=''F''}');
         h.definedone();
         num=h.num_items;
         put num=;
      end;
   enddata;
   run;
quit;
data test;set test;run;

How do I get this 2nd example to work? Is there a better way? This approach is quite common in data step processing.

Thanks,

Scott

Re: Dynamic hash column definition using existing dataset in PROC DS2

BrunoMueller — Tue, 22 Mar 2016 07:53:30 GMT

Scott

I guess you need to set the LOCKTABLE= option on both statements. See also here FedSQL Statement Table Option Syntax

See an example below:

data class class_cols;
   set sashelp.class;
   output class;
run;

proc ds2;
   data test(overwrite=yes);
      dcl package hash h();
      dcl double num;
      method init();
         if 0 then set class (locktable=share);
         h.definekey('name');
         h.definekey('sex');
         h.definedata('age');
         h.definedata('height');
         h.definedata('weight');
         h.dataset('{select * from class {options LOCKTABLE= SHARE} where sex=''F''}');
         h.definedone();
         num=h.num_items;
         put num=;
      end;
   enddata;
   run;
quit;
data test;set test;run;

Bruno

Re: Dynamic hash column definition using existing dataset in PROC DS2

ScottBass — Fri, 20 May 2016 05:57:34 GMT

Thanks that works, although I'm surprised that either the set statement or "select * from class" puts a lock on the table such that the other statement fais :-?

Re: Dynamic hash column definition using existing dataset in PROC DS2

BrunoMueller — Fri, 20 May 2016 06:05:21 GMT

Scott

I do not know why, since both are read only.

bruno

Re: Dynamic hash column definition using existing dataset in PROC DS2

ScottBass — Fri, 20 May 2016 06:09:33 GMT

Yep, my comment was more for any birdies that care to chime in. Yes, since SET and SELECT are both readonly, and explicitly supplying LOCKTABLE=SHARE works, why couldn't the underlying code do that, i.e. make both opens of the table "shared"?

Rhetorical question, no need to reply, but if any birdies want to chime in...or change the code...feel free 🙂

Re: Dynamic hash column definition using existing dataset in PROC DS2

BrunoMueller — Fri, 20 May 2016 16:19:39 GMT

Hi Scott

After reading the doc on the LOCKTABLE= table option comes the explantion

LOCKTABLE= Table Option

Details
You can lock tables only if you are the owner or have been granted the necessary privilege.
If you use PROC DS2, the default value for the LOCKTABLE option is EXCLUSIVE.

Bruno