DATA Step, Macro, Functions and more

Defining Macro Variables

Reply
Occasional Learner
Posts: 1

Defining Macro Variables

[ Edited ]

Hi guys, i am new in SAS programming and i got stuck in defining macro variable from my code:

 

/* Program */
/* ========================================================================= */
proc sql;
    select PPI into :T0_PPI_Base from Work.PPI_Base where As_of_date=&T0;
quit;

proc sql;
    select PPI into :T0_PPI_Best from Work.PPI_Best where As_of_date=&T0;
quit;

proc sql;
    select PPI into :T0_PPI_Worst from Work.PPI_Worst where As_of_date=&T0;
quit;

%let T0_PPI_Weighted=%sysevalf(&T0_PPI_Base*0.6+&T0_PPI_Best*0.2+T0_PPI_Worst*0.2);

data A;
    A=T0_PPI_Weighted;
run;

 

I would like to make a simple calculation T0_PPI_Weighted based on three defined macro variables T0_PPI_Base T0_PPI_Best T0_PPI_Worst

 

I run my code and get the below error message

 

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &T0_PPI_Base*0.6+&T0_PPI_Best*0.2+T0_PPI_Worst*0.2

 

I dont know why it can be ran and i have checked the captioned three variables are all numeric. Most Grateful if anyone could help in advising solution. Many thanks.

Respected Advisor
Posts: 2,647

Re: Defining Macro Variables

[ Edited ]
Posted in reply to Feifeitubies

Use the "Insert SAS Code" (running man icon) to properly format your code. Use copy and paste from your SAS program window.

 

No one will try to decipher your unformatted code.

 

 

--
Paige Miller
Super User
Posts: 6,522

Re: Defining Macro Variables

[ Edited ]
Posted in reply to Feifeitubies

You forgot one &.

 

Take a look at your equation for T0_PPI_Weighted.  You have ampersands before the first two macro variables (correct), but no ampersand before T0_PPI_Worst (incorrect).

 

**************** EDITED:

 

Actually, you forgot a second ampersand:

 

data A;

A = &T0_PPI_Weighted;

run;

 

Also, consider that you could eliminate macro language entirely.  You could create three tables, each containing a single variable.  Then use a DATA step to bring in the data from those three tables.

Frequent Contributor
Posts: 109

Re: Defining Macro Variables

Posted in reply to Feifeitubies

Please try the code Bellow

 

proc sql; 
select PPI into :T0_PPI_Base from Work.PPI_Base where As_of_date = &T0; 
quit; 

proc sql; 
select PPI into :T0_PPI_Best from Work.PPI_Best where As_of_date = &T0; 
quit; 

proc sql; 
select PPI into :T0_PPI_Worst from Work.PPI_Worst where As_of_date = &T0; 
quit; 

%let T0_PPI_Weighted=%sysevalf(&T0_PPI_Base*0.6 + &T0_PPI_Best*0.2 + &T0_PPI_Worst*0.2);  /*:Correction to Macro Var T0_PPI_Worst*/

data A; 
A = T0_PPI_Weighted; 
run;

 Please let us know if it worked for you.

Super User
Super User
Posts: 7,844

Re: Defining Macro Variables

[ Edited ]
Posted in reply to Feifeitubies

You are just missing the & needed to reference your macro variable in the last data step.

But don't try to use macro code to perform calculations on data.  That is not what it is for.  Macro code is useful for generating code. Use normal SAS code to manipulate data.

 

So if you really did for some reason have values in three macro variables that you wanted to combine into a value in a dataset then just let normal SAS code do the arithmetic for you.

data A; 
A = &T0_PPI_Base*0.6 + &T0_PPI_Best*0.2 + T0_PPI_Worst*0.2; 
run;

It is also not a good idea to convert numbers into strings and then back into numbers. Especially for floating point numbers since it can cause a loss of precision. 

It looks like your whole program can just be reduced to:

data a;
  merge
    PPI_Base
    PPI_Best (rename=(ppi=ppi_best))
    PPI_Worst (rename=(ppi=ppi_worst))
  ;
  by As_of_date;
  A = PPI * 0.6 + PPI_best * 0.2 + PPI_worst*0.2 ;
run;

Not sure what data the macro variable T0 has, but you could always filter down to that specific date later if you need to.

 

Super User
Posts: 22,818

Re: Defining Macro Variables

Posted in reply to Feifeitubies

@Feifeitubies note that I've formatted your code for legibility. It helps if you do that yourself in the future. Also avoid 'Hi guys' unless you only want guys to answer your question. 

 


Feifeitubies wrote:

Hi guys, i am new in SAS programming and i got stuck in defining macro variable from my code:

 

/* Program */
/* ========================================================================= */
proc sql;
    select PPI into :T0_PPI_Base from Work.PPI_Base where As_of_date=&T0;
quit;

proc sql;
    select PPI into :T0_PPI_Best from Work.PPI_Best where As_of_date=&T0;
quit;

proc sql;
    select PPI into :T0_PPI_Worst from Work.PPI_Worst where As_of_date=&T0;
quit;

%let T0_PPI_Weighted=%sysevalf(&T0_PPI_Base*0.6+&T0_PPI_Best*0.2+T0_PPI_Worst*0.2);

data A;
    A=T0_PPI_Weighted;
run;

 

I would like to make a simple calculation T0_PPI_Weighted based on three defined macro variables T0_PPI_Base T0_PPI_Best T0_PPI_Worst

 

I run my code and get the below error message

 

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &T0_PPI_Base*0.6+&T0_PPI_Best*0.2+T0_PPI_Worst*0.2

 

I dont know why it can be ran and i have checked the captioned three variables are all numeric. Most Grateful if anyone could help in advising solution. Many thanks.


 

 

Super User
Posts: 12,994

Re: Defining Macro Variables

Posted in reply to Feifeitubies

Feifeitubies wrote:

Hi guys, i am new in SAS programming and i got stuck in defining macro variable from my code:

 


How new is "new"? Unless you have a very strong background with general text processing in another programming language I would recommend not starting with macros until you have a fairly good grounding in basic SAS usage.

 

I said "general text processing" because all the macro language is designed for is to automate generation of SAS code, ie. program text.

 

For instance your code look like you are attempting to extract three values from three data sets based on the value of a another variable and then combine them. Having an explicit value of As_of_date makes me think you are going to do this for many values of the date.

 

You would likely be better off joining the three data sets on the As_of_date variable, and creating your weighted variable as a calculation for all matching values of the date in a single pass through the data. If you have a specific range of dates then that could be specified in a where clause with either the IN operator or a range using Between, or a combination.

Ask a Question
Discussion stats
  • 6 replies
  • 160 views
  • 1 like
  • 7 in conversation