04-14-2015 08:24 AM
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)
create table as
(select sum(%offsetnum("bal",startdate, enddate, 0)) as bal1
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.
04-14-2015 08:39 AM
Well, you could put in %trim() however, your cats is in the wrong place, it needs to be around the sum:
create table WANT as
select cats(BAL,put(sum(intck('month',STARTDATE,ENDDATE,0)),best.) as BAL1
I.e. concatenate the string in BAL, to the result of the sum of intck, put to a text string.
04-14-2015 08:44 AM
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
create table as
select sum(cats("bal",intck('month',datejul(startdate),enddate) + 0)) as ball
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?
04-14-2015 08:55 AM
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:
create table as(
select age, sum(bal1) as bal1
group by age)
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.
04-14-2015 11:13 AM
So you could put that in a macro as:
%macro do_something (the_number=);
create table WANT as
select age, sum(&THE_NUMBER.) as bal1
group by age;
%macro do_something (the_number=bal6);
04-14-2015 11:31 AM
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:
select %offsetnum("bal", startdate, enddate, 0) into : varname;
select sum(&varname) as bal1;
04-14-2015 01:23 PM
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.
04-14-2015 03:07 PM
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!
04-15-2015 01:30 AM
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.