BookmarkSubscribeRSS Feed
Fluorite | Level 6

Hi all,


I have a macro %calcCost(Column1,column6) which uses the information contained in column1 and column6 to perform some calculation and it then assign a value to a refence called Calcost. The calculation it performs is dependent on the information contained in Column1 and column6.

I want to apply this macro to each row of my data to populate a new column, called column99.
The code need to do the following:
start with row 1
1. run macro %cost_byrow which will call macro %calcCost(info in Column1 of row1, info in column6 of row1). e.g. %calcCost(10%,   190)
2. macro generates a value stored in reference &CalcCost
3. populate column99 with value from &CalcCost
repeat for next row
until end of data.



Here is the code I'm using.  the issue is the the code in red is producing a table with just one record. How can I obtain add a column, column99, to my data "Tiering.Data" which contains the calculated CalcCost for each observations?


%macro AMC;

%do ID=1 %to &nobs;



data Tiering.AMC;
set Tiering.Data;
            %if _n_ = &ID. %then;
             column99 = &CalcCost;


%mend AMC;


%macro cost_byrow;
%global chargerate Amount;

data _null_;
set Tiering.Data(where=(rownum=_n_));
call symputx('chargerate',Column1);
call symputx('Amount',Column6);

%mend cost_byrow;





Super User

I bet you a nice pint of beer that you don't need any macro language (at least no macro code) to solve your issue.


Post some example data in a data step (see my footnotes), and a sample of the expected output from that.


Right now your code looks like an honorable mention in the Obfuscated SAS Code Contest.

Diamond | Level 26 RW9
Diamond | Level 26

There is several things wrong here.  Firstly you don't need macro code for this, and using it is the reason you are having problems.  Use Base SAS which is the programming language.  Second off, you are mixing macro and datastep.  You need to understand that Macro is not the executable code, it just generates some text which then goes into the compiler - hence why you should use it sparingly and only for specific circumstances, not for every bit of code you do.  



%if _n_ = &ID. %then;

_n_ is a datastep automatic variable, it is not available to the macro processor.  This will not work.





column99 = &CalcCost;

Calcost is not defined in the code you provide nor is &nobs



%do ID=1 %to &nobs;

There is no associated %end stament with this.


%global chargerate Amount;

Its really a bad idea to create global macro variables from within a macro, you could alter something at higher levels.  


At the end of the day, this code could be a simple datastep - provide test data in the form of a datastep and we can provide working code.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3 in conversation