topic Re: Skip Marco iteration if datafile less than certain records in SAS Programming

Skip macro iteration if data file less than certain records

hhchenfx — Wed, 31 Jan 2018 16:19:24 GMT

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.

%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;

Re: Skip Marco iteration if datafile less than certain records

RW9 — Wed, 31 Jan 2018 13:55:33 GMT

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.

Re: Skip Marco iteration if datafile less than certain records

hhchenfx — Wed, 31 Jan 2018 13:59:02 GMT

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

Re: Skip Marco iteration if datafile less than certain records

Astounding — Wed, 31 Jan 2018 14:00:33 GMT

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

Re: Skip Marco iteration if datafile less than certain records

RW9 — Wed, 31 Jan 2018 14:01:43 GMT

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?

Re: Skip Marco iteration if datafile less than certain records

Kurt_Bremser — Wed, 31 Jan 2018 14:27:44 GMT

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.

Re: Skip Marco iteration if datafile less than certain records

srinath3111 — Wed, 31 Jan 2018 14:49:55 GMT

%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;

Re: Skip Marco iteration if datafile less than certain records

Kurt_Bremser — Wed, 31 Jan 2018 14:46:38 GMT

Wow.

data _NULL_;

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

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

Re: Skip Marco iteration if datafile less than certain records

Satish_Parida — Wed, 31 Jan 2018 14:58:58 GMT

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;

Re: Skip Marco iteration if datafile less than certain records

Reeza — Wed, 31 Jan 2018 16:17:27 GMT

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;

Re: Skip Marco iteration if datafile less than certain records

hhchenfx — Wed, 31 Jan 2018 19:53:17 GMT

WOW that's great.

Thanks a lot.