Juletip #11 - SAS-code syntax coloring in macros and Warning free SQL joins using *

CKjeldsen — Wed, 14 Dec 2016 20:31:52 GMT

I know you are all waiting anxiously to receive the next tip; thus, you get it a little early - and it even contains two tips. So you can wait for tomorrow to read the second tip….

Have you ever been annoyed by the fact that SAS-code is not syntax colored inside a macro? I.e. the following (please copy and paste the code into EG or the Enhanced Editor, as the color highlighting in the embedded SAS-code is different from these):

%macro nocolor;
	data CLASS;
	set SASHELP.CLASS;
	run;
%mend;

Enterprise Guide and the enhanced editor actually contains two “features” to enable the SAS-code syntax coloring. The first method is to create an empty dummy macro in the beginning of your real macro. If you end the dummy macro using the name, then the SAS-code syntax coloring magically appears. The second method is to create a local macro variable, that you assign the value %nrstr(%mend) – and yet again the SAS-code syntax coloring appears.

/* Examples on how to "enable" coloring of SAS Code inside macro */
%macro color; 
%* Empty dummy macro to enable syntax coloring;
	%macro bringBackColor; %mend bringBackColor; 
%* Normal SAS code with syntax coloring;
	data CLASS;
	set SASHELP.CLASS;
	run;
%mend;

%macro color2;
%* Local dummy variable to enable syntax coloring;
	%local a; %let a = %nrstr(%mend);   
%* Normal SAS code with syntax coloring;
	data CLASS;
	set SASHELP.CLASS;
	run;
%mend;

Whether these two ”features” continue to exist in future releases of EG and the Enhanced Editor is uncertain, as it is referred to as unexpected behavior according to this: http://support.sas.com/kb/34/953.html.

However, for now you may get your SAS-code syntax coloring back in macros.

The second tip is for all you lazy people (like myself) out there. When I do an SQL join with two tables, where both tables uses the same name for the key variable, then I get a warning if I simply type select *. In most cases, I am too lazy to write the names of all the columns in one of the tables, therefore I utilize SAS dataset options to rename the key variable in one of the input tables and when appropriate drop the renamed variable in my output dataset. To be able to execute the example it requires you to download and extract the two attached SAS datasets to your SAS environment, and change the libname accordingly.

libname my_lib "C:\Temp";

/* Join with Warning */
proc sql;
	create table tmp as
	select *
	from  my_lib.order_fact a
		, my_lib.employee_organization b
	where
		a.employee_id=b.employee_id
	;
quit;
/* Join without Warning */
proc sql;
	create table tmp2(drop=emp_id) as
	select *
	from  my_lib.order_fact a
		, my_lib.employee_organization (rename=(employee_id=emp_id))b
	where
		a.employee_id=b.emp_id
	;
quit;

Happy Holidays!

Re: Juletip #11 - SAS-code syntax coloring in macros and Warning free SQL joins using *

dafata39 — Tue, 27 Oct 2020 22:20:33 GMT

This is a great solution, thanks for taking the time to share it.

topic Juletip #11 - SAS-code syntax coloring in macros and Warning free SQL joins using * in SAS Community Nordic

Juletip #11 - SAS-code syntax coloring in macros and Warning free SQL joins using *

Re: Juletip #11 - SAS-code syntax coloring in macros and Warning free SQL joins using *