Conditionally execute a macro based on proc sql into var?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Conditionally execute a macro based on proc sql into var?

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.


Accepted Solutions
Solution
‎03-09-2016 05:57 PM
Super User
Posts: 19,157

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

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

View solution in original post


All Replies
Solution
‎03-09-2016 05:57 PM
Super User
Posts: 19,157

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

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

Frequent Contributor
Posts: 90

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

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.

Super User
Posts: 11,134

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

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;

Super User
Super User
Posts: 7,720

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

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.

 

 

 

Frequent Contributor
Posts: 90

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

"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

Frequent Contributor
Posts: 90

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

thanks for the data step example.

Super User
Super User
Posts: 7,720

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

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.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 487 views
  • 3 likes
  • 4 in conversation