DATA Step, Macro, Functions and more

how do I recognise the returned value of a macro as a variable in Proc SQL

Reply
Contributor
Posts: 29

how do I recognise the returned value of a macro as a variable in Proc SQL


Hi,

I'm trying to use a macro to dynamically return a a text string that I would like proc sql to recognise as a variable to sum, however, it recognises as a character and fails.  any ideas?

%macro offsetnum(variable,startdate, enddate, plus);

cats(&variable,intck('MONTH',datejul(&startdate),&enddate) + &plus)

%mend;

proc sql;

create table as

(select sum(%offsetnum("bal",startdate, enddate, 0)) as bal1

;

quit;

I know that it resolves to "bal x" so I need to also strip the blank (in data step i used left, but sql is a bit more fussy) but I also know that even when the space has been removed I get the "ERROR: The SUM summary function requires a numeric argument" message.

any ideas?

Thanks

Super User
Super User
Posts: 7,942

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

Well, you could put in %trim() however, your cats is in the wrong place, it needs to be around the sum:

proc sql;

  create table WANT as

  select  cats(BAL,put(sum(intck('month',STARTDATE,ENDDATE,0)),best.) as BAL1

  from    HAVE;

quit;

I.e. concatenate the string in BAL, to the result of the sum of intck, put to a text string.

Super User
Posts: 7,768

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

Keep in mind that the SAS macro preprocessor does not make "functions" like the functions in programming languages. Macros do not have a "return value"!

All a macro does is write text to the execution buffer, in your case the whole cats(.....) line (just replacing the macro referencing), which then causes the ERROR in SQL (use MLOGIC and MPRINT system options to make your macro processor more "talkative", and you will see what actually happens).

Everything that is not a macro function/reference is written to the execution buffer "as is", in your case the cats() call.

Your macro in the current form replaces its invocation with

cats("bal",intck('month',datejul(startdate),enddate) + 0)

which creates a proc sql like

proc sql;

create table as

select sum(cats("bal",intck('month',datejul(startdate),enddate) + 0)) as ball

;

quit;

Now, during the execution of the SQL, the cats function delivers the string balNNN (NNN being the result of the intck function + 0, converted to char), which will be undigestible for the sum() function.

What do you actually want to achieve?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 29

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to KurtBremser

there is a variable in the source table (i forgot to type in the sql!) called Bal1 (actually multiple) and I want SQL to sum the values of Bal1 by a group by function (also not in the example) - very sloppy - sorry.  something like this:

proc sql;

create table as(

select age, sum(bal1) as bal1

from tablesource

group by age)

;

quit;

the variable to sum won't always be called bal1 but I want to call it bal1, e.g. it might be bal6 = sum(bal6) as bal1

hope that makes sense.

A

Super User
Super User
Posts: 7,942

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

So you could put that in a macro as:

%macro do_something (the_number=);

proc sql;

     create table WANT as    

     select age, sum(&THE_NUMBER.) as bal1

     from tablesource

     group by age;

quit;

%mend do_something;

%macro do_something (the_number=bal6);

Super User
Posts: 5,499

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

Have you tried making this a two-step process?  Step 1 stores the word BAL6 as a macro variable, and Step 2 uses BAL6 in the current CREATE statement.  You'll have to work out the need to remove blanks here and there, but the idea would be along these lines:

proc sql;

   select %offsetnum("bal", startdate, enddate, 0) into : varname;

   select sum(&varname) as bal1;

quit;


Respected Advisor
Posts: 4,920

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

CATS returns a string which is not a numeric value. You might want to output the result of the CATS expression to a macro variable before using it in SQL.

PG

PG
Contributor
Posts: 29

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

Thanks everyone for your thoughts and suggestions - I must apologise as I described the problem badly and further thinking has resulted in me realising that this isn't the answer to my problem anyhow!  I will make sure I think on this and if I ever come up with an answer I'll update this thread - sorry!

Super User
Posts: 7,768

Re: how do I recognise the returned value of a macro as a variable in Proc SQL

Posted in reply to to_the_point

Anytime when you end up with some convoluted piece of programming, it's good to question the original intent. Complicated code often signals a wrong view of the problem.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 8 replies
  • 442 views
  • 1 like
  • 5 in conversation