Desktop productivity for business analysts and programmers

proc sql send result count in macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

proc sql send result count in macro

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


Accepted Solutions
Solution
a week ago
Occasional Contributor
Posts: 15

Re: proc sql send result count in macro

Hello ,

 

I have used the SAS code for the macro . It's a excellent code . I can obtain the result of count in the log and in a table Man Wink

 

options compress=yes sortsize=max;

/* call database */

libname b8x meta library="my_data_base_john_doe";

/* macro to count lines */

%macro countrecs (dsn=);

   proc sql noprint;

   create table counts as 
   
   select count(*) as record_count 
   
   from &dsn;

   quit;

   data _null_;

      set counts;

      call symputx('nb_line', record_count, 'G');

   run;

%mend countrecs;

proc sql inobs=2500;

/* see several information on table */

create table consultation_abcd as select * from b8x.abcd; 
create table consultation_efgh as select * from b8x.efgh;

run; 

%countrecs (dsn=b8x.AB4D)

%put Number of lines: &nb_line;

Inside this SAS code I have had my proc sql.

 

Thanks for you help Smiley Wink

View solution in original post


All Replies
Regular Contributor
Posts: 186

Re: proc sql send result count in macro

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

Super User
Posts: 5,380

Re: proc sql send result count in macro

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

Data never sleeps
Super User
Super User
Posts: 7,682

Re: proc sql send result count in macro

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.

Regular Contributor
Posts: 186

Re: proc sql send result count in macro

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

Super User
Super User
Posts: 7,682

Re: proc sql send result count in macro

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.

Regular Contributor
Posts: 186

Re: proc sql send result count in macro

I agree, a good Point Smiley Happy

//Fredrik

Super User
Posts: 7,386

Re: proc sql send result count in macro


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,353

Re: proc sql send result count in macro

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?
Super User
Posts: 7,386

Re: proc sql send result count in macro

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 15

Re: proc sql send result count in macro

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 .

@KurtBremser

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 .

Super User
Super User
Posts: 7,682

Re: proc sql send result count in macro

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.

Occasional Contributor
Posts: 15

Re: proc sql send result count in macro

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.
Regular Contributor
Posts: 186

Re: proc sql send result count in macro

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

Highlighted
Super User
Super User
Posts: 7,682

Re: proc sql send result count in macro

Me too!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 323 views
  • 12 likes
  • 7 in conversation