Create new file using macro

[hhchenfx]

Hi Everyone,

After a long process, my program generate an Error_file.

What I want is that: If there is No record in Error_file, give notice "No Error". If there is record, give notice "Having ERROR".

This notice will be created in a SAS file name NOTE1 and will export to Excel.

I try the code below but somehow it doesn't work.

Can you please help me?

Thank you,

KEY:

Most SAS data steps stop when SAS reads past the end of the input (either the SET/MERGE/UPDATE statement or the INPUT statement).  So if you want to do something when there are no observations on the input do it BEFORE the SET statement.

data error_file;
input var;
datalines;
1
2
;

	proc sql noprint;	
	select count(*) into : N_no_project_code from error;
	quit;
	%put &N_no_project_code;


	data note1; set _NULL_;		
	%macro ex();
	data note1; 
	if eof then do;	
		%if &N_no_project_code=0 %then %do;	topic="Project code: no problem";		%end;
		%else %do;topic="Project code: MISSING CODE!!! "; %end;
	output;
	end;

	SET note1 end=eof;
	run;
	%mend;
	%ex();

HHCFX

data error;
input var;
datalines;
1
2
;

	proc sql noprint;	
	select count(*) into : N_no_project_code from error;
	quit;
	%put &N_no_project_code;


	data note1; set _NULL_;		
	%macro ex();
	data note1; set note1 end=eof;
	output;
	if eof then do;	
		%if &N_no_project_code=0 %then %do;	topic="NO ERROR";		%end;
		%else %do;topic="HAVING ERROR!!! "; %end;
	output;
	end;
	run;
	%mend;
	%ex();

[Quentin]

When you say it doesn't work, what do you mean?

Do you get an error?  (I wouldn't think so, from looking at the code, it looks like it should run successfully).

Are you getting surprising results?  If so, what is the unexpected result you are getting?  Can you post the log from running this section of the code?  Are you sure that when your program runs without errors, work.no_project_check will be created and will have 0 observations?

[hhchenfx]

The macro doesn't create anything. 

[Quentin]

Please post your log from running the sql step and then running the macro.

[Tom · Accepted solution]

There a logical error there and a programming style error.

For the style error, don't define a macro in the middle of a data step. Define the macro at the top of the program and then call it where you want it to run.  You are just confusing yourself by mixing the definition of a macro into the code for a data step.

For the logic error:

Most SAS data steps stop when SAS reads past the end of the input (either the SET/MERGE/UPDATE statement or the INPUT statement).  So if you want to do something when there are no observations on the input do it BEFORE the SET statement.

Let's ignore your code and look at your problem description.  You have an existing dataset named ERROR_FILE.  You want to make a new dataset named NOTE1 that will have a character variable indicating if there were any problems.

So if you just want to test if there are any records in the file use the NOBS= option on the SET statement to count for you.

data note1;
  length topic $100 ;
  if nobs then topic=catx(' ','Found',put(nobs,comma32.),'errors.');
  else topic='No errors';
  stop;
  set error_file (drop=_all_) nobs=nobs;
run;

[hhchenfx]

Yes, Tom. 

I move the SET to the end and it works!

Thanks a lot.

HHCFX

data error_file;
input var;
datalines;
1
2
;

	proc sql noprint;	
	select count(*) into : N_no_project_code from error;
	quit;
	%put &N_no_project_code;


	data note1; set _NULL_;		
	%macro ex();
	data note1; 
	if eof then do;	
		%if &N_no_project_code=0 %then %do;	topic="Project code: no problem";		%end;
		%else %do;topic="Project code: MISSING CODE!!! "; %end;
	output;
	end;

	set note1 end=eof;
	run;
	%mend;
	%ex();