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(¯o_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=¯o_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(¯o_var_columns_n));
%PUT ************* I AM HERE: at the begining CREATE ARCHIVE TABLES ***********;
PROC SQL;
CREATE TABLE &output_table AS
SELECT
%SYSFUNC(CATS(&, ¯o_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.
... View more