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

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

to_the_point — Tue, 14 Apr 2015 12:24:29 GMT

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

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

RW9 — Tue, 14 Apr 2015 12:39:23 GMT

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.

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

Kurt_Bremser — Tue, 14 Apr 2015 12:44:03 GMT

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?

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

to_the_point — Tue, 14 Apr 2015 12:55:24 GMT

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.

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

RW9 — Tue, 14 Apr 2015 15:13:10 GMT

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

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

Astounding — Tue, 14 Apr 2015 15:31:39 GMT

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;

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

PGStats — Tue, 14 Apr 2015 17:23:22 GMT

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.

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

to_the_point — Tue, 14 Apr 2015 19:07:07 GMT

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!

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

Kurt_Bremser — Wed, 15 Apr 2015 05:30:03 GMT

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.