BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisBrooks
Ammonite | Level 13

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

7 REPLIES 7
mkeintz
PROC Star

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7
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
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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?

ChrisBrooks
Ammonite | Level 13

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.

DCL
Obsidian | Level 7 DCL
Obsidian | Level 7
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
ChrisBrooks
Ammonite | Level 13

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.

DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

Hi Chris,

 

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

 

Thanks,
Dilip

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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