topic Re: Conditionally execute a macro based on proc sql into var? in SAS Programming

Conditionally execute a macro based on proc sql into var?

kjohnsonm — Wed, 09 Mar 2016 22:37:57 GMT

Hello I need to conditionally execute a second sas program only if I have one or more obs in a data set of path\files searching for metadata? This is what I have:

proc sql noprint;
select 
	count(*) into: obs_count
from list_of_files;  /*zero, one or many fully qualified path names like C:\mypath\myfile.mdb where databasetype is set to mdb below, and pname is set to my program path*/
quit;
;
%put "&pname.file-inventories-&databasetype..Part2.sas";
%macro check_if_need_to_run;
	%if ( obs_count ne 0 ) %then %do;
	    %put &obs_count;
		%include "&pname.file-inventories-&databasetype..Part2.sas";
	%end;
 	%else %do;
		%put "No &pname.file-inventories-&databasetype..Part2.sas data to process";
	%end;
%mend check_if_need_to_run;
%check_if_need_to_run;

...at this point it is executing no mater what. Can anyone give me a pointer? TIA.

Re: Conditionally execute a macro based on proc sql into var?

Reeza — Wed, 09 Mar 2016 22:43:45 GMT

You refer to obs_count instead of the macro variable &obs_count?

Re: Conditionally execute a macro based on proc sql into var?

ballardw — Wed, 09 Mar 2016 22:46:38 GMT

If this isn't a typo when posting:

%if ( obs_count ne 0 ) %then %do;

it should be

%if ( &obs_count ne 0 ) %then %do;

Re: Conditionally execute a macro based on proc sql into var?

kjohnsonm — Wed, 09 Mar 2016 22:57:53 GMT

I had tried that once, I must have had two bugs in it at once and took it back out. Thanks for the typo catch. Since you were first to reply you get the resolve. Sorry to both of you for such a dumb question. Still green behind the ears with SAS.

Re: Conditionally execute a macro based on proc sql into var?

RW9 — Thu, 10 Mar 2016 09:31:17 GMT

Out of interest, why? To my mind a process should execute fully though the whole code, the only thing which would change is the output itself.

However if you must do it this way:

data _null_;
  set sashelp.vtable (where=(libname="WORK" and memname="LIST_OF_FILES" and NOBS > 0));
  call execute("%include '&pname.file-inventories-&databasetype..Part2.sas';");
run;

So the call execute only executes if there is an observation from the set and this only happens if the dataset list_of_files is in work and has observations. However I do still think you should look at the logic of the process and attempt to re-work it.

Re: Conditionally execute a macro based on proc sql into var?

kjohnsonm — Thu, 10 Mar 2016 16:35:03 GMT

"Out of interest, why? "

I am not sure if you are asking why I am using a proc SQL + into var, or why am I conditionally executing code, if the later when my program that starts way back with a search and find all zip files on an entire file large system and copy them to a new subdirectory tree bla bla bla... the short of it is, if my program finds a data type that SAS can scan at the metadata level then I want to do it, else skip that file type becasue reading a none existing file even once for metadata bombs out SAS and it will not auto read the reast of my files.

Sorry if that is not what you were looking for, if why proc SQL verse data step I am more familiar with proc SQL than data steps is the only reason. -KJ

Re: Conditionally execute a macro based on proc sql into var?

kjohnsonm — Thu, 10 Mar 2016 16:36:46 GMT

thanks for the data step example.

Re: Conditionally execute a macro based on proc sql into var?

RW9 — Fri, 11 Mar 2016 09:34:54 GMT

Yes, sorry, quite general, I mean of course why conditionally execute the code. The reason is that a bit of re-qorking you can avoid all of that. For example you want to run a macro when there appears in a directory a file with _abc_:

filename tmp pipe 'dir "<your_directory>\*.zip" /b';
data _null_;
  infile tmp;
  length buffer $200;
  input buffer $;
  if index(upcase(buffer),"_ABC_") > 0 then 
    call execute(cat('%your_macro(f=',strip(buffer),');'));
run;

The above code reads in the directory listing from the path (/b for only filenames), and if the substr _ABC_ is found in the filename, then a call is put out for the macro to execute with the filename. If no files have the substring, then the datastep finishes having executed all code. It just seems a simpler processing than checking each time, and running some bits.