BookmarkSubscribeRSS Feed
Feifeitubies
Calcite | Level 5

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.

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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
Astounding
PROC Star

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.

Satish_Parida
Lapis Lazuli | Level 10

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.

Tom
Super User Tom
Super User

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.

 

Reeza
Super User

@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.


 

 

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 928 views
  • 1 like
  • 7 in conversation