Help using Base SAS procedures

PROC DS2 - END= dataset option

Accepted Solution Solved
Reply
Contributor DCL
Contributor
Posts: 40
Accepted Solution

PROC DS2 - END= dataset option

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

 

 

 


Accepted Solutions
Solution
2 weeks ago
Super Contributor
Posts: 390

Re: PROC DS2 - END= dataset option

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

View solution in original post


All Replies
Valued Guide
Posts: 947

Re: PROC DS2 - END= dataset option

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?

Contributor DCL
Contributor
Posts: 40

Re: PROC DS2 - END= dataset option

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
Contributor DCL
Contributor
Posts: 40

Re: PROC DS2 - END= dataset option

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;

end;

 

But, now I get following errors:

rloop=1

ERROR: Integer divide by zero

ERROR: Integer divide by zero

ERROR: General error

ERROR: Access violation

ERROR: Access violation

 

 

Does anybody have any clue on this?

Super Contributor
Posts: 390

Re: PROC DS2 - END= dataset option

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.

Contributor DCL
Contributor
Posts: 40

Re: PROC DS2 - END= dataset option

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
Solution
2 weeks ago
Super Contributor
Posts: 390

Re: PROC DS2 - END= dataset option

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

Highlighted
Contributor DCL
Contributor
Posts: 40

Re: PROC DS2 - END= dataset option

Hi Chris,

 

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

 

Thanks,
Dilip

 

 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 173 views
  • 1 like
  • 3 in conversation