topic Re: How to apply multiple do loops to scan through multiple libraries and datasets efficiently in New SAS User

How to apply multiple do loops to scan through multiple libraries and datasets efficiently

bdemir — Wed, 17 Oct 2018 23:50:00 GMT

I am trying to do a couple do loops (or if there is anything more efficient I would like to hear) where I need to first go to the regional library. Each library can have multiple states. The code below works but when it goes through the regional folders, it goes through all 52 states and gives an error for the states that do not exist in that folder. Is there a more efficient/ cleaner log way where if the state is not in the regional folder, SAS skips it without an error message?

%syslput STATE1 = AK;
.
.
.
%syslput STATE52 = WY;

%let STATE1 = AK;
.
.
.
%let STATE52 = WY;

%syslput REGION1 = R1;
.
.
.
%syslput REGION8 = R9;

%macro onest;
%do r = 1 %to 8;
	%syslput r = &R;
 %do s = 1 %to 52;
 	%syslput s = &S;
		rsubmit;
		proc sql;
		Connect to oracle
			(path = xxxx user = &user pw = &pw);
		create table &USER..&&STATE&S.._RX_HEADER as
		 select * 
			from connection to oracle
			(
			  select distinct a.*
		  		from M_&&REGION&R.._OWN.&&STATE&S.._RX a 
		   
			);
		disconnect from oracle;
		quit;
		endrsubmit;

		%end;
	   %end;

%mend onest;
%onest

Re: How to apply multiple do loops to scan through multiple libraries and datasets efficiently

Reeza — Thu, 18 Oct 2018 02:57:27 GMT

Do you have to use Pass through SQL or is a libname or a PROC SQL an option? If they are you may have more options on how to solve something like this. For example you could check for the existence of a table first.

Re: How to apply multiple do loops to scan through multiple libraries and datasets efficiently

bdemir — Thu, 01 Nov 2018 16:38:26 GMT

Thanks for getting me on the right track. I ended up putting

%if %sysfunc(exist(dataset)) %then %do;

%end;

around my code and it gave me a log if the dataset existed in that loop.