BookmarkSubscribeRSS Feed
to_the_point
Calcite | Level 5


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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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?

to_the_point
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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;


PGStats
Opal | Level 21

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
to_the_point
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1295 views
  • 1 like
  • 5 in conversation