Skip macro iteration if data file less than certain records

[hhchenfx]

Hi Everyone,

In each iteration, my macro will create a new file and run the analysis.

However, I dont want to run the analysis for a new file with less than 20 records.

Can you please fix my code below?

Many thanks.

HC

%Macro running_over_all_data(var_name = , value=);
	
	data _TEMP; set original_file;
	keep date tradetim	;
	     IF &var_name=&value;
	run;
	
			*if too small number of record --> no running code;
			data _NULL_;
				call symput('nobs',put(x,best.));
				set _TEMP nobs=x;
				stop; 
				put nobs;

	%IF &NOBS > 5 %THEN %DO;
	        %include "C:\Users\Main analysis code.sas" /lrecl=50000;
	%END;
%Mend;

[RW9]

data _null_;
  set sashelp.vtable (where=(libname="WORK" and memname="ORIGINAL_FILE" and nobs > 20));
  call execute('%include "c:/users/main analysis code.sas" / lrecl=5000;');
run;

No need for all that code, the set will only create an observation if more than 20 records are present, if there is no observation then the call execute does not get generated.  Simple.

[hhchenfx]

In my case, I might have to SQL inside Macro to get the file.

[RW9]

You can put any code you like before this, SQL, SAS anything.  I don't see what that has to do with the question you asked?

[Astounding]

Your DATA _NULL_ step needs a RUN statement.  Without it, macro language examines &NOBS before executing the DATA step.

[Kurt_Bremser]

Since your data step has not yet run when the %if condition is evaluated (it will start to run once a step boundary is detected, which happens at the first proc or data stament in the included file), &nobs does not exist (or it could exist in the global symbol table with a wrong value).

Add a run statement before your %if.

[srinath3111]

%Macro running_over_all_data(var_name = , value=);

data  _TEMP;

set original_file;

keep date tradetim ;

%IF &var_name=&value;

run;

*if too small number of record --> no running code;

data _NULL_;

%do i=1 %to nobs;

set _TEMP ;

%if &i ge 20 then do;

%include "C:\Users\Main analysis code.sas" /lrecl=50000;

%END;

%Mend;

[Kurt_Bremser]

Wow.

data _NULL_;

set _TEMP (where=(_N_>=20) then  Do;

If you ever get that to run, you're McGyver.

[Satish_Parida]

Corrections to your macro: Hope it helps.

%Macro running_over_all_data(var_name = , value=);
	
	data _TEMP; set original_file;
	keep date tradetim	;
	     IF &var_name=&value;
	run;                                                *Not Touching your Data step;
	
			*if too small number of record --> no running code;
			Proc Sql noprint;
                        select count(*) into :NOBS from _TEMPl;
                        Quit;                *Taking count of the whole table;

	%IF &NOBS > 5 %THEN %DO;
	        %include "C:\Users\Main analysis code.sas" /lrecl=50000;
	%END;
%Mend;

[Reeza · Accepted solution]

Another version - SQL creates the count automatically so that's a change and then if you're running this multiple times you should clean up the _temp data set so the next time you run it you aren't hitting an old data set. 

%Macro running_over_all_data(var_name = , value=);
	
    proc sql;
    create table _temp as
    select date, tradetim
    from original_file
    where &var_name=&value;
    quit;

    %let nobs=&sqlobs;


	%IF &NOBS > 5 %THEN %DO;
	        %include "C:\Users\Main analysis code.sas" /lrecl=50000;
	%END;

    %*always clean up when you Are going to run a macro multiple times;
    proc sql noprint;
    drop table _temp;
    quit;

%Mend;

[hhchenfx]

WOW that's great.

Thanks a lot.

HC