topic Re: Calculating a new column using macro based on information contained in existing column in SAS Procedures

Calculating a new column using macro based on information contained in existing column

krish_101 — Mon, 18 Jun 2018 10:37:21 GMT

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;

%cost_byrow;

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

%end;

run;
%mend AMC;

%macro cost_byrow;
%global chargerate Amount;

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

%mend cost_byrow;

Thanks,

Krish

Re: Calculating a new column using macro based on information contained in existing column

Kurt_Bremser — Mon, 18 Jun 2018 11:01:26 GMT

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.

Re: Calculating a new column using macro based on information contained in existing column

RW9 — Mon, 18 Jun 2018 12:10:38 GMT

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.

This:

%if _n_ = &ID. %then;

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

This:

column99 = &CalcCost;

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

This:

%do ID=1 %to &nobs;

There is no associated %end stament with this.

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.