macro programming

[shingMAK]

Hi all,

I have background on VBA programming, however, I got trouble in using macro in SAS. It seems macro is not totally like to create a function.  Can anyone help me, please?

There are many fields in my dataset and I would like to pro-rata adjust the value of them with a total value.  So, I would like to prepare a function/subroutine/macro.

Problem 1.  I wounder why the value of dummy will be changed when the value of n1 changed.

>>>%let dummy=sum(&n1,&n2,&n3);

>>>&n1=&n1/&dummy*&c;

>>>&n2=&n2/&dummy*&c;   *<<<  the value of dummy is changed, due to the n1 changed in last row;

Problem 2.

I tried to add the function %EVAL but failed, the error is

when the co1 pass to the macro, the compiler convert the col1 to string

the EVAL only can handle numeric number

==================================

Here is the example code.

data testdata;

  input col1 col2 col3 col4 col5;

  datalines;

 1 1 3 2 2

 2 2 4 5 5

 10 10 3 7 7

  ;

run;

%MACRO prorata3(c,n1,n2,n3);

%let dummy=sum(&n1,&n2,&n3);

&n1=&n1/&dummy*&c;

&n2=&n2/&dummy*&c;   *<<<  the value of dummy is changed, due to the n1 changed in last row;

&n3=&n3/&dummy*&c;

%MEND prorata3;

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;

===============================================

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

[Tom]

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;

[shingMAK]

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;

[Tom]

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;

[shingMAK]

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;

[ballardw]

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.

[shingMAK]

I am sorry, as my computer setting do not allow to copy text out from SAS, I need to input the code separately and screen capture separately.


This is the code.
The error screen is captured in the word document.
&nbsp;
I think the error in this row
%do i=1 %to %sysfunc(countw(&amp;varlist,%str( )));
&nbsp;
====
&nbsp;
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 = &amp;serial then do;
total=sum(of &amp;varlist);
%do i=1 %to %sysfunc(countw(&amp;varlist,%str( )));
%let var=%scan(&amp;varlist,%str( ));
&amp;var = (&amp;target/total)*&amp;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;

[Tom]

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

[shingMAK · Accepted solution]

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;

[Tom]

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