%let chg_pct=%sysevalf(100*((&N_NEW-&N_OLD)/&N_OLD));
%if &chg_pct. >= &mint. and &chg_pct. <= &maxt. %then
%do;
proc sql;
UPDATE test1.TC_INPUTS2
SET status = 'pass'
WHERE vendorname = &VENDOR. and testcasenum=3.1
AND tablename = &cur_tblname1
AND columnname = &cur_colname1;
quit;
%end;
%else
%do;
proc sql;
UPDATE test1.TC_INPUTS2
SET status = 'fail'
WHERE vendorname = &VENDOR. and testcasenum=3.1
AND tablename = &cur_tblname1
AND columnname = &cur_colname1;
quit;
%end;
here is my code. pls help me to get it corrected.
the value of chg_pct is resolved to : -33.33333333
and value of N_NEW is resolved to : 2
and value of N_OLD is resolved to : 3
and value of mint is resolved to : 0
and value of maxt is resolved to : 0
The problem arises on your %IF/%THEN statement. Macro language applies %EVAL to all %IF conditions, and the decimal point in &CHG_PCT causes a problem for %EVAL. Switch to:
%if %sysevalf(&chg_pct. >= &mint.) and %sysevalf(&chg_pct. <= &maxt.) %then
That should take care of it.
Just a side note ... perhaps these data values are just for testing. But note that the %IF conditions would only be true when &chg_pct = 0.
Not sure why you are using macro language to do "data" processing. Better to use Base SAS which is for this:
proc sql; UPDATE test1.TC_INPUTS2 SET status = 'pass' WHERE vendorname = &VENDOR. and testcasenum=3.1 AND tablename = &cur_tblname1 AND columnname = &cur_colname1 and 100 * ((&n_new.-&n_old.)/&n_old.) between &mint. and &maxt. ; quit;
It sounds to me like you are in all kinds of a pickle trying to force your code through macro language. Look at what you have to start with, and what you want out, its almost 99% guarenteed that you can do what you want in Base SAS without any macro language.
Hi,
actually Iam using this macro becoz I am using loop which can take more than one value and i have to perform the same operations everytime .
Again, its all in the thinking about the problem, logically, from a Base SAS perspective, and then adding macro in when needed, not the other way round. Maybe post some of what you are trying to do, for instance:
1) Create a dataset of your looping data from the database - no need to query this once per iteration, do it once, and store it in a dataset
2) Look at this dataset, perform your calculations here - no need to do them in macro code
3) Merge this information with your working data
No need for looping.
what i actually need is how to compare the 2 variables where one of them is -ve decimal and other one is integer.
Will try to get an example when I get home, but why not:
proc sql; insert into TEST1.TC_INPUTS2 select case when int(YOUR_VARIABLE)=YOUR_VARIABLE then "Pass" else "Fail" end from HAVE; quit;
Avoid whenever possible mixing numerical values with calculations and comparisons in macros.
Do the calculation and comparison in a data _null_ step, and generate a flag macro variable instead (call symput).
And to further simplify your code, the only thing differing is the word pass/fail. Perhaps generate that directly, and avoiding the %if as well.
The problem arises on your %IF/%THEN statement. Macro language applies %EVAL to all %IF conditions, and the decimal point in &CHG_PCT causes a problem for %EVAL. Switch to:
%if %sysevalf(&chg_pct. >= &mint.) and %sysevalf(&chg_pct. <= &maxt.) %then
That should take care of it.
Just a side note ... perhaps these data values are just for testing. But note that the %IF conditions would only be true when &chg_pct = 0.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.