I have below sas macro.I want to create a dataset where I want to multiply each year with some weight
for each variables in the macro variable chk_var.But it is not giving any result .In log it is showing
Any help will be appreciated.
My macro -
%let chk_var=Total_Shareholders_Funds Secured_Loans Total_Debt___Loan_Funds
Total_Liabilities Sundry_Debtors Inventories Cash_and_Bank_Balance
Total_Current_Assets Total_Current_Liabilities Sls_Turnover_Operat_Incom
Net_Sales Total_Income Operating_Profit Interest Gross_Profit Profit_Before_Tax Tax
Reported_Net_Profit Adjusted_Net_Profit Debt_Equity_Ratio Interest_Cover_Ratio
ROCE RONW;
%put %sysfunc(countw(&chk_var.));
options mprint mlogic symbolgen;
%macro weight_avg;
Data capital_all_3;
set capital_all_2;
%do i=1 %to %sysfunc(countw(&chk_var.));
%let a=%scan(&chk_var.,&i.);
%if year=2015 %then &a._15=6*&a.;
%if year=2014 %then &a._14=5*&a.;
%if year=2013 %then &a._13=4*&a.;
%if year=2012 %then &a._12=3*&a.;
%if year=2011 %then &a._11=2*&a.;
%if year=2010 %then &a._10=1*&a.;
%end;
run;
%mend weight_avg;
%weight_avg;
Please use {i} above your post to post code in as that is a code window. Also, provide test data so we know what your data looks like and can run something. Also, provide what the output should look like, we don't see any of this.
Now, this is guessing from your code. What you have done is the classic, mixing up macro code and datastep code which doesn't work. Macro Code is there to generate text strings. Base SAS is there to actually execute. So no your code will not work. Macro does not have access to the data variable year. What would I do, well I would first go to the actual programming language - that is Base SAS - and do the processing in there:
data capital_all_3; set capital_all_2; array vars{*} Total_Shareholders_Funds Secured_Loans Total_Debt___Loan_Funds Total_Liabilities
Sundry_Debtors Inventories Cash_and_Bank_Balance Total_Current_Assets
Total_Current_Liabilities Sls_Turnover_Operat_Incom Net_Sales Total_Income
Operating_Profit Interest Gross_Profit Profit_Before_Tax Tax Reported_Net_Profit
Adjusted_Net_Profit Debt_Equity_Ratio Interest_Cover_Ratio ROCE RONW; do i=1 to dim(vars{*}); select(year); when (2010) vars{i}=1 * vars{i}; when (2011) vars{i}=2 * vars{i}; ... end; run;
This uses an array over the variables you have specified (and to note, I would drop the %let and put the varaibles in the array as you may hit macro limits there), and a nicer select clause to choose which to do.
Remember, Macro language is not a replacement for Base SAS!!
Without question this is the right approach. When you want to apply the same logic to a set of variables, think of arrays. That's what they are for.
You can speed up the program somewhat by replacing the code following the array statement:
factor = year - 2009;
if (1 <= factor <= 6) then do i=1 to dim(vars);
vars{i} = vars{i} * factor;
end;
If you really want to create new variables, rather than replace the existing variables, come up with a second array statement that contains the new names with the "_15" added. The creation of the new names could be automated using macro language.
Please provide some sample data (a data step creating ds capital_all_2).
What certainly won't work with your macro as intended: %if year=2015 %then &a._15=6*&a.;
Think of macros a "pre-processing". So first comes the macro processor and does everything "macro" so everything with "&" and "%". Only once that's done the SAS processor comes into play to deal with the "remainder".
On macro level everything works "textual". Your condition is:
%if year=2015
The string year is never equal to the string 2015
And that's why you get this message: MLOGIC(WEIGHT_AVG): %IF condition year=2015 is FALSE
This bit here would then actually almost work: %then &a._15=6*&a
Let's say &a contains Total_Shareholders_Funds - once the macro processor is done with it the remainder for the SAS processor would be: Total_Shareholders_Funds_15 = 6*Total_Shareholders_Funds
What would be missing is the semicolon as this one has already been consumed by the macro processor for ending the %if statement.
When trying to write macros always first write the code in plain SAS syntax without macro coding. Only once that works start to "macrotize" the code. That makes debugging much easier.
...and always try to get things done with "plain" Base SAS only using SAS Macro language if there is really a need for it.
Hi.
As @Patrick pointed, you are mixing compile time with run time.
By removing the % from the if then statement, you are transforming the statement as a run time statement, which is the right thing as YEAR variable value will only be available at run time.
That's assuming YEAR is a variable of the capital_all_2 dataset.
If that's the case, then te folowing will work.
%macro weight_avg;
Data capital_all_3;
set capital_all_2;
%do i=1 %to %sysfunc(countw(&chk_var.));
%let a=%scan(&chk_var.,&i.);
if year=2015 then &a._15=6*&a.;
if year=2014 then &a._14=5*&a.;
if year=2013 then &a._13=4*&a.;
if year=2012 then &a._12=3*&a.;
if year=2011 then &a._11=2*&a.;
if year=2010 then &a._10=1*&a.;
%end;
run;
%mend weight_avg;
The do end macro cycle is just there to automatically produce the code for you at compile time.
Hope it helps.
Daniel Santos @ www.cgd.pt
You have two additional potential issues involved with this code. You would create variables such as Total_Shareholders_Funds_14, Total_Shareholders_Funds_15 and so on. First potential issue that can catestrophically fail is the addition of 3 chracters may result in varaible names that exceed the 32 character limit. Depending on where you exceed the length you may end up with a varaible ending in _1 or just _ .
Second the creation of multiple variables with data in the name (the two digit year) makes these values very cumbersome to deal with. Next year's data would require additonal variables and all the processing code would require adding the additonal variables. You would almost certainly be better off with either a new (single) name such as &a.wt (still with the character limit concern), leaving as is or possibly adding an actual weight variable. And then process BY Year for most things.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: