topic Create new file using macro in SAS Programming

Create new file using macro

hhchenfx — Sat, 23 Mar 2019 03:44:44 GMT

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();

Re: Create new file using macro

Quentin — Sat, 23 Mar 2019 02:59:36 GMT

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?

Re: Create new file using macro

hhchenfx — Sat, 23 Mar 2019 03:18:57 GMT

The macro doesn't create anything.

Re: Create new file using macro

Tom — Sat, 23 Mar 2019 03:24:29 GMT

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:

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;

Re: Create new file using macro

hhchenfx — Sat, 23 Mar 2019 03:43:10 GMT

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();

Re: Create new file using macro

Quentin — Sat, 23 Mar 2019 03:44:22 GMT

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