topic Re: PROC DS2 - END= dataset option in SAS Procedures

PROC DS2 - END= dataset option

DCL — Tue, 03 Oct 2017 12:40:30 GMT

Hi,

I just realized that dataset END option is not possible in DS2:

do until(eof);

set datasetName (locktable=share) end=eof;

ERROR: Compilation error.

ERROR: Parse encountered END when expecting ';'.

ERROR: Line 0: Parse failed: datasetName (locktable=share) >>> end <<< =eof; arrayName[arKey

My intention is to load the lookup data to temporary array so that I could use it while processing the main fact table.

I managed to load the arrays using a data-> run method

data _null_;

method run();

set datasetName (locktable=share);

arrayName[arKey, arIndex] = dsField1;

end;

enddata;

Now, I need to access this array while processing my fact table. Unfortunately I am short of ideas to use it in the same DATA step in DS2.

Could anybody please help me?

Thanks in advance!

P.S- I have multiple look up tables to be loaded into arrays and it is too big for loading into hashes

Re: PROC DS2 - END= dataset option

mkeintz — Tue, 03 Oct 2017 17:37:24 GMT

Could you put the readin of data for the lookup table in a method init? Then the run method could access the lookup values,right?

Re: PROC DS2 - END= dataset option

DCL — Wed, 04 Oct 2017 02:21:20 GMT

Hi Mark,

From what I understood only run method has the implicit loop to read through all the dataset observations. Can init be used to read through all the observations?

Thanks
Dilip

Re: PROC DS2 - END= dataset option

DCL — Wed, 04 Oct 2017 12:39:58 GMT

I did try to load the lookup tables in init() :

method init();

if 0 then set dsName (locktable=share);

do rLoop=1 to &dsObs.;

put rLoop=;

set dsName (locktable=share);

arrayName[arKey + 1,arIndex] = dsField1;

end;

But, now I get following errors:

rloop=1

ERROR: Integer divide by zero

ERROR: General error

ERROR: Access violation

Does anybody have any clue on this?

Re: PROC DS2 - END= dataset option

ChrisBrooks — Wed, 04 Oct 2017 14:58:04 GMT

Can you tell us why you want to use END - it's not clear to me why it's necessary in this case. If you give us some more of the code we might be able to offer an alternative way of doing what you want.

Re: PROC DS2 - END= dataset option

DCL — Wed, 04 Oct 2017 17:44:15 GMT

Hi Chris,

I want to convert something like following into DS2:
data output;

retain f1 0;
array ar1(%eval(&maxLookupKey.+1),3) 8 _temporary_;
IF _N_=1 THEN DO;
do until(eof1);
set lookup1 end=eof1;
ar1(lookupKey+1,1)=f1;
ar1(lookupKey+1,2)=f2;
ar1(lookupKey+1,3)=f3;
end;
END;
set factTable;
by field1;

if first.field1 then do;
f1=ar1(field1+1,1);
.
.
.
end;

.
.
.
run;

So, i am stuck with DS2 while trying to read through the datasets that I use to load into arrays which I can later use while processing the fact table.

I hope the above code helps in conveying my requirement. If not, tomorrow I shall give a working version of the code with some sample data.

Thanks,
Dilip

Re: PROC DS2 - END= dataset option

ChrisBrooks — Thu, 05 Oct 2017 00:51:20 GMT

You can load the contents of a data set into an array in the INIT method if you use the SQLSTMT package http://documentation.sas.com/?docsetId=ds2ref&docsetTarget=p154ftg7h2x9zqn11o5g7yltm3cl.htm&docsetVersion=9.4&locale=en and use a FEDSQL SELECT to bring in one row at a time with the FETCH method inside a loop.

I've created a small example program which first creates two data sets and then loads one into an array inside the INIT method. Then, when the RUN method executes the contents of that array are retrieved and used to calculate the product of the number from the array and the second data set.

data one;
	infile datalines;
	input x;
	datalines;
2
4
6
8
;
run;

data two;
	infile datalines;
	input y;
	datalines;
10
12
14
16
;
run;

proc ds2;
	data three(keep=(x,y,z)) / overwrite=yes;
		dcl int n[4];
		dcl int counter x y z;
		retain counter;
		method init();
			dcl package sqlstmt stmt('select x from one');
			
			stmt.execute();
			stmt.bindResults([x]);
			counter=1;
			do while (stmt.fetch()=0);
				n[counter]=x;
				counter=counter+1;
			end;
		end;
		
		method run();			
			set two;
			if _n_=1 then counter=1;			
			x=n[_n_];			
			z=x*y;
			counter=counter+1;					
		end;	
	enddata;
run;
quit;

You should be able to use this technique of loading the array in the INIT method in your code - please let me know if there's anything in there you don't follow.

Re: PROC DS2 - END= dataset option

DCL — Thu, 05 Oct 2017 07:58:06 GMT

Hi Chris,

This helped, Thanks a lot for being so kind to illustrate the functionalities of SQLSTMT package!

Thanks,
Dilip