Help using Base SAS procedures

Dynamic hash column definition using existing dataset in PROC DS2

Accepted Solution Solved
Reply
Super Contributor
Posts: 376
Accepted Solution

Dynamic hash column definition using existing dataset in PROC DS2

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


Accepted Solutions
Solution
‎05-20-2016 01:54 AM
SAS Super FREQ
Posts: 682

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

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

View solution in original post


All Replies
Solution
‎05-20-2016 01:54 AM
SAS Super FREQ
Posts: 682

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

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

Super Contributor
Posts: 376

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

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 :-?

SAS Super FREQ
Posts: 682

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

Scott

 

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

 

bruno

Super Contributor
Posts: 376

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

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 Smiley Happy

SAS Super FREQ
Posts: 682

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 412 views
  • 1 like
  • 2 in conversation