SAS - A Macro following a proc sql called in a macro

Question

New here, so if I did something wrong, I apologize. I'm new user of SAS as well.

I created a macro that calls first a proc sql that creates a certain table that I want to pass it to another macro (inside the first macro).

%Macro Mc_Copy_Table (TABLE_NAME);
  proc sql;
    create table &TABLE_NAME as 
    select *
    from OR_IN.&TABLE_NAME;

    connect using OR_OUT;
    execute (truncate table &TABLE_NAME) By OR_OUT;
    disconnect from OR_OUT;
  quit;

  %MC_obsnvars(&TABLE_NAME);

  %put &Nobs;
  %if &Nobs > 100000 %then
    %do; /* use of the sql loader */
    proc append base = OR_OU. &TABLE_NAME (&BULKLOAD_OPTION)
                data = &TABLE_NAME;
    run;
    %end;
  %else
    %do;
    proc append base = OR_OU. &TABLE_NAME (Insertbuff=10000)
                data = &TABLE_NAME;
    run;
    %end;
%Mend Mc_Copy_Table;

The Mc_Obsnvars macro use the attrn function to get the number of observations from the given dataset (it opens the dataset first). Depending on the number of observations, my program either use the sqlloader or not. OR_IN and OR_OUT are libnames (oracle engine).

When The macro Mc_Copy_Table is executed, with let's say TABLE1 as argument, the Mc_Obsnvars is executed first which tries to open TABLE1 which doesn't exist yet. The proc sql is executed afterwards.

Why the macro is executed before the proc sql ? and is there any way to have the proc sql be executed first ? putting the proc sql part in a macro doesn't solve the problem. Thanks :)

If I understood your question, you want to execute Proc Sql first and time then calculate number of observations. If that's correct, you don't require two macros. Please remove %Mc_Obsnvars(&TABLE_NAME) and run your code. — Praneeth Rachumallu
Is there a semicolon to end the create table statement before your macro call? If not, then the %Mc_Obsnvars() will be executed before the create table is executed. Where does your PROC SQL step end? Also, is %Mc_Obsnavars() generating any SAS code, or is it just populating a macro variable? — Quentin
I edited the code to make it clearer. Thank you for your responses. — Spoutnik

DomPazz DomPazz · Accepted Answer · 2017-05-17T16:52:27

I think you have a syntax issue, as Quentin alludes to in his comment. This works OK for me:

%macro copy_table(intable, outtable);
proc sql noprint;
create table &outtable as
select * from &intable;

%count_obs(&outtable);
%put NOBS:&nobs;
quit;
%mend;

%macro count_obs(table);
%global nobs;
select count(*) into :nobs trimmed from &table;
%mend;

data test;
do i=1 to 10;
    output;
end;
run;

%copy_table(test,test2);

Note however, you don't have to do the count. There is an automatic variable from PROC SQL called &sqlobs with the number of records returned from the last query.

So this gives you what you are looking for, I think:

%macro copy_table(intable, outtable);
proc sql noprint;
create table &outtable as
select * from &intable
where i < 5;

%let nobs=&sqlobs;
%put NOBS:&nobs;
quit;
%mend;
%copy_table(test,test2);

SAS - A Macro following a proc sql called in a macro

1 Answers