The expected output should be
200 | 200 | 600 | 2 | 2 |
250 | 250 | 500 | 5 | 5 |
435 | 435 | 130 | 7 | 7 |
But the output is
200 | 5 | 14 | 2 | 2 |
250 | 8 | 15 | 5 | 5 |
435 | 22 | 7 | 7 | 7 |
Oh yeah, I got that !
Thank you for all your help.
This my code finally.
data testdata;
input serial col1-col5 ;
datalines;
1 1 1 3 2 2
2 2 2 4 5 5
3 10 10 3 7 7
;
%macro prorate(serial,target,varlist);
%local i var ;
if serial = &serial then do;
total=sum(of &varlist);
%do i=1 %to %sysfunc(countw(&varlist,%str( )));
%let var=%scan(&varlist,&i);
&var = round((&target/total)*&var) ;
%end;
end;
drop total;
%mend prorate;
options mprint;
data temp;
set testdata;
%prorate(serial=1, target=1000, varlist=col1 col2 col3);
%prorate(serial=2, target=10000, varlist=col2 col3 col4);
%prorate(serial=3, target=400, varlist=col1 col2 col3 col4 col5);
run;
/* turn off the mprint */
options nomprint;
You cannot make a FUNCTION in the macro language. The macro processor is just what its name says. It is a macro processor, that is a pre-processor of the source that can replace the text of the source code with other source code.
So in your first example you put some text into the macro variable DUMMY. You then used that text to generate a SAS assignment statement.
Say the values of the macro variables N1 to N3 and C are the strings A, B, C and X. Then this statement:
%let dummy=sum(&n1,&n2,&n3);
Will result in DUMMY having the text
sum(A,B,C)
Which you then use to generate the statements:
A=A/sum(A,B,C)*X;
B=B/sum(A,B,C)*X;
So if you used the values of A B and C to change A and then use the new value of A to change B.
Before you start trying to use the macro language to write some SAS code for you make sure you know what SAS code you want to run.
What SAS code did you run to get the expected result?
I suspect that your problem has nothing to do with using macro logic and is instead just a confusion about the order of operations. If you continually re-calculate the SUM and in between the calculations you change the values of the variables used to create the sum then things will be different.
So if you used the values of COL1 to COL3 to change COL1 and then use the new values of COL1 to change COL2.
Just calculate the sum FIRST and REMEMBER it.
data testdata;
input col1-col5 ;
datalines;
1 1 3 2 2
2 2 4 5 5
10 10 3 7 7
;
data temp;
set testdata;
array prorate col1-col3;
sum_prorate=sum(of prorate[*]);
do index=1 to dim(prorate);
prorate[index] = prorate[index]/sum_prorate*1000;
end;
run;
Thank you for your reply.
The original code of data step is given below. It is simple and direct but is too long, and doing similar work with different parameter only, so that I would like to simply it by creating something like function/macro.
While I understand in SAS, I cannot create a function to do so, but....is that any other way to simply the code?
===================
Data abcde;
set abcde;
if serial = 1 then do;
*** protatra col1, col2, col3 to 200
end;
if serial = 2 then do;
*** protatra col3, col6, col8 to 155
end;
if serial = 3 then do;
*** protatra col7, col10, col15 to 4779
end;
if serial = 888 then do;
*** protatra col12, col2, col5 to 177777
end;
What code? You left out the actual code for the part that is repeating.
If you did want to write a macro to help you generate that code it looks like it would need only TWO parameters. Or perhaps THREE if you want the macro to also generate the IF statement.
I am not sure what PROTATRA means or how to pronounce it. So let me use PRORATE as the name of the macro as at least it is something I can pronounce.
%prorate(serial = 1, target=200, varlist=col1 col2 col3)
%prorate(serial = 2, target=155, varlist=col3 col6 col8)
%prorate(serial = 3, target= 4779, varlist=col7 col10 col15)
%prorate(serial = 888, target= 177777, varlist=col12 col2 col5)
Now if the logic of "protatra" is similar to the code in your first post then the macro definition might look like this:
%macro prorate(serial,target,varlist);
%local i var ;
if serial = &serial then do;
total=sum(of &varlist);
%do i=1 to %sysfunc(countw(&varlist,%str( ));
%let var=%scan(&varlist,%str( );
&var = (&target/total)*&var ;
%end;
end;
drop total;
%mend;
The code is very useful. I got the idea, but I don't know what is the error about....Is that the problem of the SAS software in my computer?
I try to use the code from you like this the character bold is edited by me.
There is a warning for the %do i=1 to %sysfunc(c..., so it is updated as %to
add some closing bracket added
ERROR: %EVAL Function has no expression to evaluate, or %IF statement has no condition.
ERROR: Argument 2 to macro function %SCAN is not a number
ERROR: The macro stop
%macro prorate(serial,target,varlist);
%local i var ;
if serial = &serial then do;
total=sum(of &varlist);
%do i=1 %to %sysfunc(countw(&varlist,%str( )));
%let var=%scan(&varlist,%str( ));
&var = (&target/total)*&var ;
%end;
end;
drop total;
%mend prorate;
For future questions please include the text from the log of the code plus any generated error messages.
If you want to see your error messages in better relation to the entire code generated use the system options Mprint (and possibly Mlogic if the issue involves logic in the macro language or Symbolgen when variable resolution is the issue.
Example:
options mprint; data temp; set testdata; %prorata3(1000,col1,col2,col3); /* if there exists any other condition later %prorata4(10000,col2,col3,col4); %prorata5(400,col1,col2,col3,col4,col5); */ run; /* turn off the mprint */ options nomprint;
In general the Macro language resolves before a data step executes as the macro generates code that, hopefully, works in the context provided. As such the values of data step variables are generally not seen by the macro code. The SAS macro language is designed for generating text, basically code. The general approach to macro coding is:
1) write SAS program code that does what you want
2)identify the parts that need to be reused or "dynamic" (data set names, variable name (not values so much) lists, repetitive blocks of code)
3) write macro code to replace/use the logic of the dynamic portions
While it is possible to write some "function" like macros the restrictions and complexity can be pretty extreme.
If you want to use values of data step variables they you may want to look into PROC FCMP that allows you to write more general functions that look like what you attempted.
That is a common mistake and the error message points directly to the problem.
ERROR: Argument 2 to macro function %SCAN is not a number
You left the index number out of the call to %SCAN().
%let var=%scan(&varlist,&i,%str( ));
Oh yeah, I got that !
Thank you for all your help.
This my code finally.
data testdata;
input serial col1-col5 ;
datalines;
1 1 1 3 2 2
2 2 2 4 5 5
3 10 10 3 7 7
;
%macro prorate(serial,target,varlist);
%local i var ;
if serial = &serial then do;
total=sum(of &varlist);
%do i=1 %to %sysfunc(countw(&varlist,%str( )));
%let var=%scan(&varlist,&i);
&var = round((&target/total)*&var) ;
%end;
end;
drop total;
%mend prorate;
options mprint;
data temp;
set testdata;
%prorate(serial=1, target=1000, varlist=col1 col2 col3);
%prorate(serial=2, target=10000, varlist=col2 col3 col4);
%prorate(serial=3, target=400, varlist=col1 col2 col3 col4 col5);
run;
/* turn off the mprint */
options nomprint;
Remember that %EVAL() can only do integer arithmetic. So you can use it to do things like:
%let total=%eval(100 + 200);
But if you start trying to use floating point values or to division you will get strange results since it does only INTEGER arithmetic. If you need to use floating point values then use %SYSEVALF() macro function instead.
%let average=%sysevalf(100/200);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.