proc sql send result count in macro

[azertyuiop]

Hello / good morning ,

I want generate a count with a proc sql and send the result in a variable. I have report the macro and the variable.

%mend test_macro;
 
proc sql;
 
/* count line */
 
create table my_new_table as
 
select count(id) as nb_line 
into :nb_line
from database.original_table;
 
run;
 
%test_macro;
 
/* print resultat in exit/newpaper of SAS */
 
%put number of line : &nb_line;

The macro isn't read by SAS.

This message appear to in bleu/green in the exit/newspaper of SAS.

%put number of line : &nb_line; /* */
WARNING: Apparent symbolic reference nb_line not resolved.

If you see the problem can give me your opinion to fix this bug ?

Thanks for your help

[FredrikE]

If you create the macro variable inside a macro it is local to tha macro.

If so, make it global as the first step of your program:

%global nb_line;

//Fredrik

[LinusH]

Why are you even use a macro? You don't have any parmeters... What is the end goal for this logic?

[RW9]

Look up macro variable scope in the documentation, that macro variable is local to the macro call so removed after that macro is resolved.  I agree with @LinusH that there is no need for this code, and I advise against creating global macro variables, if its just your code running then it may be fine, but on any enterprise system or multi-user etc. you could get all kinds of unwanted results.

[FredrikE]

But the global macro variable is only global to the current SAS session ,right?

So it should only be a problem if several users with different programs access the same session?!

//Fredrik

[RW9]

Depends on the setup.  Anyways, you should always aim to write code which is nicely encapsulated, imagine if another user uses your macro code and it changes their macro variables, it can create really problematic debug issues.  And rarely is there a need for global ones anyway, some system level setups maybe, but actual code, never.  There are many methodologies to avoid such things.

[FredrikE]

I agree, a good Point 🙂

//Fredrik

[Kurt_Bremser]

---

@azertyuiop wrote:

%mend test_macro;
 
proc sql;
 
/* count line */
 
create table my_new_table as
 
select count(id) as nb_line 
into :nb_line
from database.original_table;
 
run;
 
%test_macro;
 
/* print resultat in exit/newpaper of SAS */
 
%put number of line : &nb_line;

---

I see a %mend without a %macro, so this code piece won't work at all.

Make sure that you post your whole code.

The log:

ERROR: No matching %MACRO statement for this %MEND statement.
24         %mend test_macro;
25         
26         proc sql;
27         
28         /* count line */
29         
30         create table my_new_table as
31         
32         select count(id) as nb_line
33         into :nb_line
34         from database.original_table;
WARNING: INTO clause is ignored in the CREATE TABLE statement.
ERROR: Libref DATABASE is not assigned.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35         
36         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
37         
38         %test_macro;
           _
           180
WARNING: Apparent invocation of macro TEST_MACRO not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

39         
40         /* print resultat in exit/newpaper of SAS */
41         
42         %put number of line : &nb_line;
WARNING: Apparent symbolic reference NB_LINE not resolved.
number of line : &nb_line

The only error that could be ignored is the one about the missing library.

[Astounding]

There are a host of issues with this code, which makes it a shot in the dark as to how to correct it.  However, the issue of %LOCAL vs. %GLOBAL is not one of them.  The PROC SQL appears outside of a macro definition, and thus has to create a %GLOBAL macro variable.

• Does %TESTDATA create database.original_table?  If so, %TESTDATA needs to run before PROC SQL can process the output data set.
• PROC SQL should end with QUIT; not with RUN;
• Why is PROC SQL creating a table?  You can create a macro variable without creating a table.
• What does the log say about the results of the PROC SQL?
• Do you really want a count of ID, vs. a count of distinct ID?  Do you just want a count of all the observations in the database.original_table?

[Kurt_Bremser]

Actually,

proc sql;
 
/* count line */
 
create table my_new_table as
 
select count(name) as nb_line
into :nb_line
from sashelp.class;
 
run;

produces this log:

26         proc sql;
27         
28         /* count line */
29         
30         create table my_new_table as
31         
32         select count(name) as nb_line
33         into :nb_line
34         from sashelp.class;
WARNING: INTO clause is ignored in the CREATE TABLE statement.
NOTE: Table WORK.MY_NEW_TABLE created, with 1 rows and 1 columns.

which means that the into: won't work anyway.

[azertyuiop]

Hello or Good morning ,

@FredrikE

I have tryed the instruction " %global nb_line; " but there is again the even message in green/blue.

@LinusH

I use a macro to stock the quantity of line in a variable to reuse the result in several counts.

@RW9

Thanks for the technical information about number of user and macro in SAS .

@Kurt_Bremser

I correct the structure of the programm with %macro and %mend.

I correct the structure of my program to test this.

@Astounding

" Does %TESTDATA create database.original_table? " > The macro " %TESTDATA " must be only save the result count of proc sql.

" Why is PROC SQL creating a table? " > I want to stock the result of count in a variable because when I use the instruction "count"  in my PROC SQL the result it's an easy board with only one cell .

" What does the log say about the results of the PROC SQL? " > I want see in the log the result with this function " %put number of line : &nb_line; " which give the quantity of line in my table.

" Do you really want a count of ID, vs. a count of distinct ID?  Do you just want a count of all the observations in the database.original_table? " > I want count all line of the table without a distinct .

[RW9]

So why do you need this setup:

%put number of line : &nb_line;

I am asking for two reasons, firstly if your not doing generic code it will just make your code more complex and liable to fall over, and secondly macro variables are text, so numbers are just text leading to conversions and such like.  Use Base SAS all the time, it has all the functionality you need.  If you have to do this, then consider this code (i have corrected all the mistakes as well):

%macro test_macro;
 
  proc sql noprint;
    select count(age) as nb_line 
    into :nb_line
    from sashelp.class;
  quit;
  &nbline.
 
%mend test_macro;
 
%put number of line : &nb_line;

The macro resolves to the string 19.  No need ot pass macro parameters back and forth or create global ones.  Even this however has pretty much zero use and doesn't really do anything. You can get a nice dataset from proc freq, or create one from proc sql and then use that data in further processing.

[azertyuiop]

I have tryed this macro :

%macro test(var=);

count(&var);

%mend;

data work.temp;
set mybdd.mytable;
call symput(myfield);

run ;

There is this message of error in logs :

ERROR 252-185: The SYMPUT subroutine call does not have enough arguments.

[FredrikE]

I feel a bit confused now, this is a new piece of code....

Have you read the documentation for call symput?

What do you want the Count statement to achieve?

Is this meta code or your actual work?

//Fredrik

[RW9]

Me too!