BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PN00429118
Calcite | Level 5

%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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

 

 

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

PN00429118
Calcite | Level 5

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 .

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

PN00429118
Calcite | Level 5

what i actually need is how to compare the 2 variables where one of them is -ve decimal and other one is integer.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

LinusH
Tourmaline | Level 20

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.

Data never sleeps
PN00429118
Calcite | Level 5
i need the macro to be in my code as I have used do looping to get the values from the sql DB and for each itteration it is taking one column and performing operations on that. here Iam doing implicit pass through
Astounding
PROC Star

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.

 

 

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
  • 8 replies
  • 8610 views
  • 1 like
  • 4 in conversation