topic Re: proc sql send result count in macro in SAS Enterprise Guide

proc sql send result count in macro

azertyuiop — Thu, 05 Oct 2017 12:36:46 GMT

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

Re: proc sql send result count in macro

FredrikE — Thu, 05 Oct 2017 12:47:27 GMT

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

Re: proc sql send result count in macro

LinusH — Thu, 05 Oct 2017 13:01:50 GMT

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

Re: proc sql send result count in macro

RW9 — Thu, 05 Oct 2017 13:15:49 GMT

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.

Re: proc sql send result count in macro

FredrikE — Thu, 05 Oct 2017 13:21:51 GMT

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

Re: proc sql send result count in macro

RW9 — Thu, 05 Oct 2017 13:29:52 GMT

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.

Re: proc sql send result count in macro

FredrikE — Thu, 05 Oct 2017 13:41:19 GMT

I agree, a good Point 🙂

//Fredrik

Re: proc sql send result count in macro

Kurt_Bremser — Thu, 05 Oct 2017 14:45:53 GMT

@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.

Re: proc sql send result count in macro

Astounding — Thu, 05 Oct 2017 14:51:56 GMT

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?

Re: proc sql send result count in macro

Kurt_Bremser — Thu, 05 Oct 2017 14:56:46 GMT

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.

Re: proc sql send result count in macro

azertyuiop — Fri, 06 Oct 2017 07:37:46 GMT

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 .

Re: proc sql send result count in macro

RW9 — Fri, 06 Oct 2017 08:21:15 GMT

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.

Re: proc sql send result count in macro

azertyuiop — Fri, 06 Oct 2017 08:32:34 GMT

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.

Re: proc sql send result count in macro

FredrikE — Fri, 06 Oct 2017 08:44:46 GMT

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

Re: proc sql send result count in macro

RW9 — Fri, 06 Oct 2017 08:50:13 GMT

Me too!

Re: proc sql send result count in macro

Kurt_Bremser — Fri, 06 Oct 2017 09:17:54 GMT

@azertyuiop wrote:

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.

You obviously have not read the documentation. Do so to avoid such extremely simple mistakes. The documentation is there for a reason, not consulting it is a sign of foolishness.

call symput() requires TWO arguments.

Re: proc sql send result count in macro

azertyuiop — Fri, 06 Oct 2017 09:24:19 GMT

Hello ,

@FredrikE and

@Kurt_Bremser

Yes I have read several documentations about SAS macro in PDF 130-30 , 243-29 , base_macro_6997 , p071-27 .

I want that the macro count the number of line in a table. Why I use a macro , because to develop my SAS program in the futur I want use several time this macro , for several count in 4 or 6 tables.

if you want give an opinion on the SAS code , my actual code is this :

%macro test(var=);

count(&var);

%mend;

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

run ;

Re: proc sql send result count in macro

Kurt_Bremser — Fri, 06 Oct 2017 09:36:59 GMT

If you had read the macro documentation, you would know how to use call symput, as it is part of the macro/data step interface.

Your code has obvious errors (I gave you the reason in my previous post), and it won't work.

Re: proc sql send result count in macro

Astounding — Fri, 06 Oct 2017 12:41:53 GMT

As you can see from all the questions here, your code is far from working. I will give you something that works (I think), but you still have a lot of studying to do to understand why this code works and your code doesn't. (At least I'm assuming this code works. It might need minor tweaking, since I can't test it.)

%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;

Then you could test it with:

%countrecs (dsn=database.original_table)

%put Number of lines: &nb_line;

Note that I removed ID from the code. Presumably, you would like your code to run even if the incoming data does not contain a field named ID.

Re: proc sql send result count in macro

ballardw — Fri, 06 Oct 2017 16:39:25 GMT

Please Provide some example input data and an example of what the final result should be. Posting non-working code does not describe the actual issue or result.

There are so many ways to count things in SAS and then combine that result with something else that choices need to be made based on something concrete. For instance if this is involved is displaying a result a human reads such as a report one way may be more appropriate than if the value is to be added to a data set and used for computation.