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

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

1 ACCEPTED SOLUTION

Accepted Solutions
azertyuiop
Quartz | Level 8

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

20 REPLIES 20
FredrikE
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

I agree, a good Point 🙂

//Fredrik

Kurt_Bremser
Super User

@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
PROC Star

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
Super User

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

Me too!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 20 replies
  • 10846 views
  • 16 likes
  • 7 in conversation