BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidTM
Fluorite | Level 6

Hello, I would like to ask you for help with macros I am programing. I am using SAS Enterprise Guide 4.3.

Here is the code I am using:

 

I am running this DATA step to run function which I defined:

OPTIONS CMPLIB = WORK.FUNCS;
DATA _NULL_;
	rc = func_join_archive_tables("ACCT_FCT", "ACCT_FCT_COLUMNS", "Y", 2014.01, 2014.03);
RUN;

 

 

Then this function will start:

PROC FCMP OUTLIB = WORK.FUNCS.SQL;
	FUNCTION func_join_archive_tables(imput_table_prefix $, macro_var_columns $, spoj_obdo_flg $, first_mth, last_mth) $;
		%GLOBAL union;
		%LET union=;

		/*rc = RUN_MACRO('macro_get_db_data');*/
		CALL EXECUTE('%macro_get_db_data');

		IF spoj_obdo_flg = "Y" THEN
			DO;
			imput_table_prefix_m = imput_table_prefix;
			macro_var_columns_m = macro_var_columns;
			first_mth_m = first_mth;
			last_mth_m = last_mth;
			*rc = RUN_MACRO('macro_get_tables_spoj_obd', imput_table_prefix_m, macro_var_columns_m, first_mth_m, last_mth_m);
			CALL EXECUTE('%macro_get_tables_spoj_obd('||imput_table_prefix_m||','||macro_var_columns_m||','||first_mth_m||','||last_mth_m||')');
			END;
		ELSE
			DO;
			_test = 1;
			i = 0;
			DO WHILE(_test);
				i + 1;
				
				_test = 0;
			END;
			END;

	RETURN ;
ENDSUB;
RUN;

 

 

That function will call macro which will collect necessary information about database and tables in it. (%macro_get_db_data) and than it will run this set of macros:

%MACRO macro_get_tables_spoj_obd(imput_table_prefix_m, macro_var_columns_m, first_mth_m, last_mth_m);
	%LOCAL cnt_loop imput_table_prefix macro_var_columns first_mth last_mth output_table input_table output_union_table;
	%LET imput_table_prefix = %SYSFUNC(QUOTE(&imput_table_prefix_m));
	%LET macro_var_columns = %SYSFUNC(DEQUOTE(&macro_var_columns_m));
	%LET first_mth = %SYSFUNC(DEQUOTE(&first_mth_m));
	%LET last_mth = %SYSFUNC(DEQUOTE(&last_mth_m));

	PROC SQL;
	CREATE TABLE WORK.GET_TABLES_SPOJ_OBD AS
		SELECT
			*

		FROM
			WORK.DB_TABLE_INFO

		WHERE
			TABLE_PREFIX = &imput_table_prefix
		AND	MesREZ >= &first_mth
		AND	MesREZ <= &last_mth
	;
	QUIT;

	PROC SQL NOPRINT;
		SELECT
			LIBNAME,
			TABLE_NAME

		INTO
			:libname1 THROUGH :libname99,
			:table_name1 THROUGH :table_name99

		FROM
			WORK.GET_TABLES_SPOJ_OBD
	;
	QUIT;
	/*%LET n_mgtso = &SQLOBS;*//* SQLOBS NOT WORKING PROPERLY */
	%PUT ******************************;
	%PUT ************GLOBAL************;
	%PUT _GLOBAL_;
	%PUT ******************************;
	%PUT &libname1;
	%PUT ******************************;

	%LET cnt_loop = 1;

	%DO %WHILE ( %SYMEXIST(%SYSFUNC(CATS(libname, &cnt_loop))) = 1 );
%PUT ************* I AM HERE: at the begining of DO WHILE LOOP ***********;	
		%LET output_table = %SYSFUNC(CATS(WORK., %SYSFUNC(CATS(&, table_name, &cnt_loop))) );
		%LET input_table = %SYSFUNC(CATS( %SYSFUNC(CATS(&, libname, &cnt_loop)), ., %SYSFUNC(CATS(&, table_name, &cnt_loop)) ));

		%IF ( %SYMEXIST(%SYSFUNC(CATS(libname, %EVAL(&cnt_loop + 1)))) = 1 ) %THEN
			%DO;
			%LET union = %SYSFUNC(CATX(%STR( ), %SYSFUNC(CATX(%STR( ), %SYSFUNC(CATX(%STR( ),&union, SELECT, *, FROM)), &output_table)), UNION));
			%END;
		%ELSE 
			%DO;
			%LET union = %SYSFUNC(CATX(%STR( ), %SYSFUNC(CATX(%STR( ),&union, SELECT, *, FROM)), &output_table));
			%END;
%PUT ************* I AM HERE: in the middle of DO WHILE LOOP ***********;	
		%macro_create_archive_tables(output_table_n=&output_table, input_table_n=&input_table, macro_var_columns_n=&macro_var_columns);

		%LET cnt_loop = %EVAL(&cnt_loop + 1);
	%END;  
%PUT ************* I AM HERE: after the end of DO WHILE LOOP ***********;
	%IF( %SYMEXIST(%SYSFUNC(CATS(libname, &cnt_loop))) = 0 ) %THEN
		%DO;
		%LET output_union_table = %SYSFUNC(CATS(%SYSFUNC(DEQUOTE(&imput_table_prefix)), _, join, &cnt_loop, mth));
		%macro_join_archive_tables(output_union_table=&output_union_table);
		%END;
%MEND macro_get_tables_spoj_obd;

%MACRO macro_create_archive_tables(output_table_n=, input_table_n=, macro_var_columns_n=);
	%LOCAL output_table input_table macro_var_columns last_flg;
	%LET output_table = %SYSFUNC(DEQUOTE(&output_table_n));
	%LET input_table = %SYSFUNC(DEQUOTE(&input_table_n));
	%LET macro_var_columns = %SYSFUNC(DEQUOTE(&macro_var_columns_n));
%PUT ************* I AM HERE: at the begining CREATE ARCHIVE TABLES ***********;
	PROC SQL;
	CREATE TABLE &output_table AS
		SELECT
			%SYSFUNC(CATS(&, &macro_var_columns))

		FROM
			&input_table
	;
	QUIT;
%PUT ************* I AM HERE: at the end of CREATE ARCHIVE TABLES ***********;
%MEND macro_create_archive_tables;

%MACRO macro_join_archive_tables(output_union_table=);
	%PUT ************* I AM HERE: at the begining of JOIN (UNION) TABLES ***********;
	PROC SQL;
		CREATE TABLE WORK.&output_union_table AS
			&union
	;
	QUIT;
	/*%SYMDEL union;*/
%MEND macro_join_archive_tables;

 

 

My problem is that first time I run my code it seems that macro variables in INTO statement in the second SQL query won't initialized.

LOG1:

******************************
WARNING: Apparent symbolic reference LIBNAME1 not resolved.
&libname1
******************************
************* I AM HERE: after the end of DO WHILE LOOP ***********
************* I AM HERE: at the begining of JOIN (UNION) TABLES ***********

 

LOG2(few tens of rows lower):

NOTE 155-205: Line generated by the CALL EXECUTE routine.
14        +    :libname1 THROUGH :libname99,    :table_name1 THROUGH :table_name99    FROM    WORK.GET_TABLES_SPOJ_OBD  ;  QUIT;
14       !+PROC SQL;   CREATE TABLE WORK.ACCT_FCT_join1mth AS      ;  QUIT;;
                                                                   _
                                                                   22
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.  

NOTE: Line generated by the CALL EXECUTE routine.
14        +    :libname1 THROUGH :libname99,    :table_name1 THROUGH :table_name99    FROM    WORK.GET_TABLES_SPOJ_OBD  ;  QUIT;
14       !+PROC SQL;   CREATE TABLE WORK.ACCT_FCT_join1mth AS      ;  QUIT;;
                                                                   _
                                                                   76
ERROR 76-322: Syntax error, statement will be ignored.

 

 

... of cource than the code stop processing and fails to execute.

 

But when I run the code for the scond (and third, ...) time the problem change. Variables seems to be initialized, however now %SYSEVALF function throw an error.

LOG:

******************************
ARCHIVE
******************************
************* I AM HERE: at the begining of DO WHILE LOOP ***********
ERROR: %SYSEVALF function has no expression to evaluate.
************* I AM HERE: in the middle of DO WHILE LOOP ***********
************* I AM HERE: at the begining CREATE ARCHIVE TABLES ***********

... but code continues running and end up with right solution.

 

Anyone has any sugestions what can possible cause the errors?

 

In advance thank you for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

David,

 

Part of the problem is the nature of CALL EXECUTE.  It executes macro language statements immediately, instead of waiting for the DATA step to end.  So you might think you are getting this:

 

Run all of %macro_get_db_data

 

Run all of %macro_get_tables_spoj_odb

 

Instead, you are actually getting:

 

Run the macro language statements in %macro_get_db_data

 

Run the macro language statements in %macro_get_tables_spoj_odb

 

Run the SAS language statements in %macro_get_db_data

 

Run the SAS language statements in %macro_get_tables_spoj_odb

 

Assuming that this should be changed, %NRSTR is the easiest way to make this happen.  It will suppress the macro language triggers until the DATA step that contains CALL EXECUTE is finished.  For example:

 

call execute ('%nrstr(%macro_get_db_data)');

 

This may not solve the entire problem but it should get you past an important hurdle.

 

Good luck.

 

View solution in original post

14 REPLIES 14
Astounding
PROC Star

David,

 

Part of the problem is the nature of CALL EXECUTE.  It executes macro language statements immediately, instead of waiting for the DATA step to end.  So you might think you are getting this:

 

Run all of %macro_get_db_data

 

Run all of %macro_get_tables_spoj_odb

 

Instead, you are actually getting:

 

Run the macro language statements in %macro_get_db_data

 

Run the macro language statements in %macro_get_tables_spoj_odb

 

Run the SAS language statements in %macro_get_db_data

 

Run the SAS language statements in %macro_get_tables_spoj_odb

 

Assuming that this should be changed, %NRSTR is the easiest way to make this happen.  It will suppress the macro language triggers until the DATA step that contains CALL EXECUTE is finished.  For example:

 

call execute ('%nrstr(%macro_get_db_data)');

 

This may not solve the entire problem but it should get you past an important hurdle.

 

Good luck.

 

DavidTM
Fluorite | Level 6

 

Dear Astounding,

 

Thank you for your advice. Unfortunately, It didn't make any difference. I also tried run my macros using RUN_MACRO(), however everything concluded into same mistakes as I described in my first post.

 

Anyway, thank you for your help.

 

Any other suggestions?

 

David

DavidTM
Fluorite | Level 6
I've managed to find cause of the second mistake with %SYSEVALF function. I was using empty macro variable '&union' in functions in my macro.

However, my first problem with not initialize​d macro variables in the first run still lasts.
DavidTM
Fluorite | Level 6

Hi guys,

 

after all I was able to solve my problem. The problem was in dead caused by that the macro "%macro_get_tables_spoj_obd" was not called entirely so when I put the call into "%NRSTR()" function the problem was solved.

First time I applied the "%NRSTR()"  function, I applied it just to first macro call "%macro_get_db_data" and I forgot about the second macro.

 

And the seconf problem with %SYSEVALF function error was caused by empty macro variable used.

 

So thank you all for the effort and your help.

 

David

Tom
Super User Tom
Super User

Can you explain what you are trying to do?  

Perhaps if you simplify your code it will be clearer where the issue is.

For example there is no reason to use %SYSFUNC() to call any of the CAT() functions in macro code. Macro expressions automatically concatenate.

 

For example if you want to attach the value of a macro varaible to a constrant string just do. No need to use CATS() function.

%let cnt_loop=1;
%put "%SYSFUNC(CATS(libname, &cnt_loop))";
%put "libname&cnt_loop";

And what are these statements trying to do?

%LET output_table = %SYSFUNC(CATS(WORK., %SYSFUNC(CATS(&, table_name, &cnt_loop))) );
%LET input_table = %SYSFUNC(CATS( %SYSFUNC(CATS(&, libname, &cnt_loop)), ., %SYSFUNC(CATS(&, table_name, &cnt_loop)) ));

Perhaps you meant something like this:

%let cnt_loop=1;
%let table_name1=FRED;
%let libname1=INLIB;

%let output_table = WORK.&&table_name&cnt_loop;
%let input_table = &&libname&cnt_loop...&&table_name&cnt_loop;
  

 

DavidTM
Fluorite | Level 6
Dear Tom,

my sets of macros are retrieving information from database and joining tables containing client's information from various months which are stored in various tables to one table.
I want to automatizate the process, in that way that all the user have to do is to sent a name of a table and range of months which he/she wants to join.
And thank you to show me the way how to simplify my code.
However, that won't solve my problem as long as the libname and table_name variables from INTO statement are not shown in GLOBAL table either.
Tom
Super User Tom
Super User

There are serious issues with DOSUBL and macro variables. It has a nasty habit of copying the values of local macro variables into global macro variables with similar names when it exits.

 

In general I find that it is much easier to simplify the data driven code generation.  One simplication is to make sure that the logic is constructed such that the generated code runs AFTER the code generation step runs.

There are two main techniques that I use. First if the code being generated is extremely simple (or can be reduced to a simple macro call) then I would use CALL EXECUTE to run the code. Make sure to use %NRSTR() around the macro call so that just the macro call is pushed onto the stack, instead of the code that the macro generates being pushed onto the stack.

 

data _null_;
   set my_data ;
   call execute(cats('%nrstr(%mymacro)','(',parm1,',',parm2,')'));
run;

The other is to write the code to a file and then use %INCLUDE to run the generated code. This has the advantage that you can use all of the report generation techniques and features of the SAS DATA step to generate the code you want. For example, as in the simple example below, if you name the variables in data the same as the macro parameter or proc options you can use the VARNAME= syntax of the PUT statement to generate both the variable (parameter/option) name and its value. Plus you can debug it by examining the file and submitting parts of it to make sure the code does what you meant.  

 

filename code temp;
data _null_;
   set my_data ;
   put '%mymacro(' parm1= ',' parm2= ')' ;
run;
%inc code / source2 ;

 In general I try to avoid generating "macro arrays". This will avoid the need to use complex nested macro variable references such as &&table_name&i .

DavidTM
Fluorite | Level 6
Dear Tom, thank you for your detailed answer and a lot of advices it contains. I wound need time to apply all of your recommendations, unfortunatelly in my work I don't have so much time as far as this is not my priority right now. Anyway I will try to adapt to what you are advicing me to do as far as I belive that its more standart way that I am using. David
Tom
Super User Tom
Super User

What is the format of the information in the table that describes the tables to be joined?

What is the format of the statement (or statements) that you are trying to generate to perform the join?

Post a simple example with two or more of the records from the metadata that describes the tables to be joined and what code you think will allow you to join them.  I do not see any ON or WHERE statements in the code that you macros look to be generating, so perhaps you are not actually "joining" the tables at all?  Perhaps you just want to concatenate the tables?

 

For example I see that your are have columns named LIBNAME and TABLE_NAME that you are referencing.  Perhaps all you need is something like this:

 

filename code temp ;
data _null_;
  file code ;
  if _n_=1 then put 'data want;' / '  set ' ;
  if eof then put '  ;' / 'run;' ;
  set DB_TABLE_INFO end=eof;
  where TABLE_PREFIX = &imput_table_prefix
    AND  MesREZ >= &first_mth
    AND  MesREZ <= &last_mth
  ;
  memname = catx('.',libname,table_name);
  put @10 memname ;
run;
%inc code / source2 ;

 

DavidTM
Fluorite | Level 6

Dear Tom,

 

as I wrote in my post my code acctualy work great and produce right outcomes (table I need). The problem is just that first run of the code always fails, because PROC SQL: INTO statement (as it seems) won't create macro variables I need.

Then in the second run it is always run without mistakes with right solution as I would expect.

The final table which is supposed to be joined from multiple tables from various months, or better code for its join is created in the loop by filling global macro variable 'union' with code of every table in range od months iteratively.

 

Thank you for your example in you post, mayby all I need is to simplyfy my code and the mistake will disappear, because it can be caused my wrong compiling or wrong filling of stack ...

 

David

Peter_C
Rhodochrosite | Level 12
Initialize them empty before the step
DavidTM
Fluorite | Level 6
Dear Peter_C,

Thank you for your advice. However I already tried that and it throws a lot of new mistakes. As far as I understand if I initialize​d the variables in LOCAL or GLOBAL statement PROC SQL INTO statement won't get access to them (or the problem is different, the point is that won't solve my problem).
Peter_C
Rhodochrosite | Level 12
That sounds like thq SQL INTO : is writing in an independent environment.
With that complexity for something basically simple I would get back to first principles and start simple.
Good luck
peterC
DavidTM
Fluorite | Level 6
Dear Peter_C,

yes probablly I am doing it wrong in a way that I am writting to complex code which is hard to manage with my skills.

Maybe I will get back to start.

Thank you.

David

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1943 views
  • 0 likes
  • 4 in conversation