topic Basic PROC DS2 Questions in SAS Programming

Basic PROC DS2 Questions

ScottBass — Mon, 22 Feb 2016 04:15:38 GMT

Hi,

Apologies for the basic questions. I've read the first 1/2 of the PROC DS2 Language Reference but still have the below issues.

proc ds2;
   data class(overwrite=yes);
      method run();
         set sashelp.class;
      end;
   enddata;
   run;
quit;

ERROR: Compilation error.

ERROR: BASE driver, schema name SASHELP was not found for this connection

ERROR: Table "SASHELP.CLASS" does not exist or cannot be accessed

ERROR: Line 26: Unable to prepare SELECT statement for table class (rc=0x80fff802U).

How do I make PROC DS2 recognize the pre-allocated SASHELP library (and WORK/other libraries)?

data class;
   set sashelp.class;
run;

proc ds2;
   data class(overwrite=yes);
      method run();
         set class;
      end;
   enddata;
   run;
quit;

ERROR: Compilation error.
ERROR: Base table or view already exists CLASS
ERROR: Unable to execute CREATE TABLE statement for table work.class.

How do I update a table in place using DS2 (if possible)?

proc ds2;
   data class2(overwrite=yes);
      method init();
         if 0 then set class (locktable=share);
      end;
      method run();
         set class (keep=(name));
         if _n_ le 7 then age=_n_;
      end;
   enddata;
   run;
quit;

The "if 0 then set..." paradigm is often used with hash object processing to dynamically set the attributes of the hash object variables. However, a caveat is the implied retain for data set variables. This program illustrates the issue; I'd want age=. for _n_ > 7.

proc ds2;
   data class2(overwrite=yes);
      method init();
         if 0 then set class (locktable=share);
      end;
      method run();
         call missing(of _all_);  * <<< ;
         set class (keep=(name));
         if _n_ le 7 then age=_n_;
      end;
   enddata;
   run;
quit;

This is how I usually deal with this issue in my "old style" data step code. But this yields:

ERROR: Compilation error.
ERROR: Missing END statement for the method run.
ERROR: Parse encountered CALL when expecting end of input.
ERROR: Line 29: Parse failed: >>> call <<< missing(of _all_);

Is there a way to explicitly set a list of variables to missing in DS2?

proc ds2;
   data test(overwrite=yes);
      method init();
         declare package hash h(
            [name],
            [age sex],
            16,
            {select name, age, sex from work.class where sex='F'}
         );
      end;
      method run();
      end;
   enddata;
   run;
quit;

WARNING: Line 27: No DECLARE for referenced variable name; creating it as a global variable of type double.
WARNING: Line 28: No DECLARE for referenced variable age; creating it as a global variable of type double.
WARNING: Line 28: No DECLARE for referenced variable sex; creating it as a global variable of type double.
ERROR: Syntax error at or near "WORK.0"
ERROR: Unable to prepare statement for hash data source 0.
ERROR: Error reported by DS2 package d2hash:
ERROR: Hash data source load failed.
ERROR: Fatal run-time error.
ERROR: General error

proc ds2;
   data test(overwrite=yes);
      method init();
         if 0 then set class (keep=(name age sex));
         declare package hash h(
            [name],
            [age sex],
            16,
            {select name, age, sex from work.class where sex='F'}
         );
      end;
      method run();
         set class (keep=(name));
         rc=h.find();
         drop rc;
      end;
   enddata;
   run;
quit;

ERROR: Compilation error.
ERROR: Missing END statement for the method init.
ERROR: Parse encountered DECLARE when expecting end of input.
ERROR: Line 27: Parse failed: >>> declare <<< package hash h(

Any hints on how to code this?

Thanks,

Scott

Re: Basic PROC DS2 Questions

ScottBass — Mon, 22 Feb 2016 05:07:46 GMT

A couple more examples...

This works:

data test;
   if 0 then set class;
   call missing(of _all_);
   if _n_=1 then do;
      declare hash h(dataset: 'class (where=(sex="F"))');
      h.defineKey('name');
      h.defineData('age');
      h.defineData('sex');
      h.defineDone();
   end;
   stop;
run;

proc ds2;
   data test(overwrite=yes);
      declare char(10) name;
      declare double age;
      declare char(1) sex;
      method init();
         declare package hash h();
         h.defineKey('name');
         h.defineData('age');
         h.defineData('sex');
         h.dataset('class');
         h.defineDone();
      end;
      method run();
         stop;
      end;
   enddata;
   run;
quit;

But this fails:

proc ds2;
   data test(overwrite=yes);
      declare char(10) name;
      declare double age;
      declare char(1) sex;
      method init();
         declare package hash h();
         h.defineKey('name');
         h.defineData('age');
         h.defineData('sex');
         h.dataset('class (where=(age=12))');
         h.defineDone();
      end;
   enddata;
   run;
quit;

proc ds2;
   data test(overwrite=yes);
      declare char(10) name;
      declare double age;
      declare char(1) sex;
      method init();
         declare package hash h();
         h.defineKey('name');
         h.defineData('age');
         h.defineData('sex');
         h.dataset({select * from class where sex='F'});
         h.defineDone();
      end;
   enddata;
   run;
quit;

Yet this works:

proc ds2;
   data test(overwrite=yes);
      method run();
         set {select * from class where sex='F'};
      end;
   enddata;
   run;
quit;

I'd like to load the hash object (package) using a where clause, like I can in the traditional data step. Do I need to use a view instead? The doc implies that I should be able to use an SQL query as a data source for the hash.

Minor: when I use PROC DS2 in EG, the created datasets don't show up in the project. Why is that? I'm using a recent version of EG (7.11 HF2 (7.100.1.2785) (32-bit)).

Re: Basic PROC DS2 Questions

JonathanWill — Thu, 29 Sep 2016 18:19:46 GMT

I had the same problem and this post was the only one that came up when I searched for the error message.

Bit more digging and it appears the issue is because SASHELP is a concatenated libname and these are not supported in DS2. See SAS usage note 51043 ( http://support.sas.com/kb/51/043.html )

Posted here in case some else comes a lookin'

Re: Basic PROC DS2 Questions

anonymous_user — Thu, 28 Sep 2017 21:14:10 GMT

Hi Scott-

Here's a bugfix for one of the examples:

proc ds2;
   data test(overwrite=yes);
      declare char(10) name;
      declare double age;
      declare char(1) sex;
      method init();
         declare package hash h();
         h.defineKey('name');
         h.defineData('age');
         h.defineData('sex');   
         h.dataset(%tslit({select * from class where sex='F'}));
        /* h.dataset({select * from class where sex='F'}); */
         h.defineDone();
      end;
   enddata;
   run;
quit;

The fix was to add single quotes outside the brackets inside the h.dataset() query. Actually, since your query contains single quotes in one of the clauses, you can't add quotes because this messes up the syntax parser. So you have to use the %TSLIT() function as per Solutions for missing DATA step features within DS2.

Now a solution to get the variable attributes into the hash without needing to explicitly define their type:

/* Create an empty class that just has the variable we need */
data skeleton_class;
	if _n_=0 then
		set class(keep=name age sex);
run;

/*
	Create a copy of the original data set. Apparently there is a
	lock error if the same class is used in the h.dataset() statement
	and the SET statement in the run() method. I tried creating this
	with the /view option, but there was still a lock error.
	A waste of resources, but maybe there is a work-around?
*/
data class_copy;
	set class;
run;

proc ds2 ;
	data test(overwrite=yes);
		declare package hash h();
		declare double rc;
		method init();

			if 0 then
				set skeleton_class;
			h.defineKey('name');
			h.defineData('name');
			h.defineData('age');
			h.defineData('sex');
			h.dataset(%tslit({select * from class where sex='F'}));
			h.defineDone();
		end;
		method run();
			set class_copy(keep=(name));
			rc=h.find();

			if rc ne 0 then
				do;
					age=-1;
					sex='X';
				end;
		end;
	enddata;
	run;
quit;

Produces as a test data set:

One thing I've observed with DS2 is that the DECLARE statements need to appear at the top of the method. So this example you gave wouldn't work:

method init(); 
    if 0 then set class (keep=(name age sex)); 
    declare package hash h( [name], [age sex], 16, {select name, age, sex from 
         work.class where sex='F'} );
end;

, regardless of the other issues, because the hash package declaration appears after the IF statement. That's why I moved it to appear before the init() method. Also I think it needs to be declared outside the methods in order for it to be available to both init() and run().