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
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.
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?
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
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);
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;
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
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!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.