topic Re: Improve Macro code that run line by line from a condition file. in SAS Programming

Improve Macro code that run line by line from a condition file.

hhchenfx — Mon, 01 Apr 2019 01:47:01 GMT

Hi Everyone,

My code below count number of record in file HAVE that meet each condition in file CONDITION.

It works fine.

Essentially, it create new file from a given line in Condition file, then turn ID and condition into macro variable and do the checking.

Can you have a look and show me any way to improve it?

Thank you,

HHCFX

data have; 
input date a b c d;
datalines;
1 4 4 5 9
2 4 4 5 9
3 4 5 5 0
4 3 6 8 9
5 3 5 0 0
6 4 5 1 2
7 6 5 9 6
;run;

data condition; 
infile datalines dlm=' ' dsd;
informat id best.
		 condition $100. ;
input id condition $;
datalines;
1 "if a=4 and b=4"
2 "if a=4 and d=9"
;run;

*get number of record in codition file;
proc sql noprint; 
select count(*) into :nobs from condition; quit;

Data report_file; set _NULL_;run;

*Macro here;
%Macro counting();

	%Do i=1 %to &nobs;
	*get condition i;
	data cond_i; set condition; 
	if _N_=&i;run;

	*turn Id into macro variable;
	proc sql noprint; select id into :id from cond_i; quit;
	%put &id;

	*turn condition into macro variable;
	proc sql noprint; select condition into :condition from cond_i; quit;
	%put &condition;

		*get temporary file that meet condition i;
		data want;set have;
			id=&id;
			&condition ;
		run;

		proc means data=want noprint;
		by id;
		var id;
		output out=temp(keep= id _FREQ_) N=count ;run;

		*add to report file;
		data report_file; set report_file temp;run;
	%end;
%mend;

%counting();

KEY

Data want;
set have end=done;
if a=4 and b=4 then cond_1+1;
if a=4 and d=9 then cond_2+1;
if done;
run;

data cond; set condition;
cond=catt(condition, ' then cond_',id,'+1;');
run;

proc sql noprint;
select cond into :condition_list separated by ' ' from cond;quit;
%put &condition_list;

Data want;set have end=done;
&condition_list;
if done;
run;

Re: Improve Macro code that run line by line from a condition file.

ChrisNZ — Sun, 31 Mar 2019 21:29:20 GMT

No need for macros. 10 line of code can to this, and will run much faster too:

data _null_;
  set CONDITION nobs=NOBS end=LASTOBS;
  if _N_=1 then do ;
    call execute ('data ');
    do I=1 to NOBS;
      call execute (catt('COND_',I));
    end;
    call execute ('; set HAVE;');
  end;
  call execute (catt(CONDITION,' then output COND_',ID,';'));
  if LASTOBS then call execute ('run;');
run;

NOTE: CALL EXECUTE generated line.
1 + data
2 + COND_1
3 + COND_2
4 + ; set HAVE;
5 + if a=4 and b=4 then output COND_1;
6 + if a=4 and d=9 then output COND_2;
7 + run;

Re: Improve Macro code that run line by line from a condition file.

ChrisNZ — Sun, 31 Mar 2019 21:30:41 GMT

Also not coding everything in lower case makes the code easier to read.

My personal choice is to use lower case for the language and upper for user-defined names.

Re: Improve Macro code that run line by line from a condition file.

Astounding — Sun, 31 Mar 2019 23:52:03 GMT

Perhaps use a version of the suggested program that counts, without creating subsets. Something like:

Data want;
set have end=done;
if a=4 and b=4 then cond_1+1;
if a=4 and d=9 then cond_2+1;
if done;
run;