topic Re: %Scan function error in SAS Programming

%Scan function error

MARY9999 — Mon, 20 Oct 2014 14:41:25 GMT

%MACRO FIND_CLOSEST_VALUE(limOrDedOption,limorDedValue);

%LET numOptions = %Sysfunc(Countw(&limOrDedOption.));

limOrDedSetFlag = 'N';

%DO curLimOrDed = 1 %TO &numOptions.;

IF limOrDedSetFlag = 'N" AND &curlimOrDed > 1 THEN DO;

lwrVal = %Scan(&limOrDedOption., &curLimOrDed. - 1);

uprVal = %Scan(&limOrDedOption., &curLimOrDed.);

IF &limOrDedValue. < uprVal THEN DO;

IF (&limOrDedValue. - lwrVal)/(uprVal - lwrVal) < 0.5 THEN &limOrDedValue = lwrVal;

ELSE &limOrDedValue = uprVal;

END;

%END;

%MEND;

Receiving an error:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

prevLimOrDed

ERROR: Argument 2 to macro function %SCAN is not a number.

Error occurs on the lwrVal. uprVal is fine. It's almost as if the if condition is not met on the &curLirOrDed > 1

Any ideas?

Re: %Scan function error

PaigeMiller — Mon, 20 Oct 2014 14:44:20 GMT

Can we see the relevant portions of the code that you used to call this macro?

Re: %Scan function error

RW9 — Mon, 20 Oct 2014 14:45:21 GMT

Hi,

Have you tried:

lwrVal = %Scan(&limOrDedOption., %eval(&curLimOrDed. - 1));

If so maybe put options mlogic mprint symbolgen; on and see what the macro variables resolve to.

Re: %Scan function error

MARY9999 — Mon, 20 Oct 2014 14:52:59 GMT

%LET numArray = 200000 300000 500000 1000000 2000000;

DATA TEST;

SET inforce;

%FIND_CLOSEST_VALUE(&numArray, testValue);

RUN;

Re: %Scan function error

MARY9999 — Mon, 20 Oct 2014 14:53:59 GMT

yes, I tried %eval and i tried put(&curLimOrDed,1.) - 1. I am receiving the exact same error =(

Re: %Scan function error

RW9 — Mon, 20 Oct 2014 15:01:52 GMT

Well, couple of things:

- You have a typo in the macro, IF limOrDedSetFlag = 'N" AND - double quotes after the N.

- Secondly you only want to do the %do loop from 2 to numoptions, other 1 with -1 = 0 which is an invalid scan array.

Note, can't actually test this as no data provided.

So change: %DO curLimOrDed = 2 %TO &numOptions.;

Re: %Scan function error

MARY9999 — Mon, 20 Oct 2014 15:08:41 GMT

For some reason cannot copy paste in this editor. I typed 'N" up, it is actually a 'N'.

As for the second comment, I have a condition to execule when &curLimOrDed = 1 and when &curLimOrDed > 1. The

%Scan(&limOrDedOption, &curLimOrDed. -1 ) executes only when :

IF limOrDedSetFlag = 'N" AND &curlimOrDed > 1 THEN DO;

Re: %Scan function error

RW9 — Mon, 20 Oct 2014 15:17:11 GMT

Ok, another thought, what is the prevLimOrDed, mentioned in the error message, I don't see this in the macro or code. Also, have you tried putting options mlogic mprint symbolgen; on and seeing what is being generated?

Re: %Scan function error

ballardw — Mon, 20 Oct 2014 15:29:33 GMT

This does not pass the VALUE of testValue variable to the macro, it passes the TEXT value "testValue";

Re: %Scan function error

MARY9999 — Mon, 20 Oct 2014 15:55:30 GMT

testValue is actually a dataset column. The macro should check what the value in the testValue column is.

For example, say the value is 60, but acceptable values are 50 and 100.

The macro calculates if 60 is closer to 50 or 100, and based on that, resets the testValue to 50.

Re: %Scan function error

MARY9999 — Mon, 20 Oct 2014 16:01:27 GMT

The prevLimOrDed was me testing. sorry, removed it, still getting an error:

Re: %Scan function error

RW9 — Mon, 20 Oct 2014 17:03:09 GMT

Perhaps start again, post the actual code (including call), the actual log text with the error, and if possible some test data which triggers the error. Will have a look tomorrow.

Re: %Scan function error

art297 — Mon, 20 Oct 2014 18:17:13 GMT

MARY9999: Do you have to use a macro for this? It can be done easily within a datastep. E.g.:

DATA TEST;

SET sashelp.class (rename=(age=testValue));

array limOrDedOption(8) _temporary_ (10.4,11.6,12.4,13.6,14.4,15.6,16.4,17.6);

do _n_=2 to dim(limOrDedOption);

if limOrDedOption(_n_) ge testValue then do;

if testValue+(limOrDedOption(_n_)-limOrDedOption(_n_-1))/2 gt limOrDedOption(_n_)

then closest=limOrDedOption(_n_);

else closest=limOrDedOption(_n_-1);

leave;

end;

else if _n_ eq dim(limOrDedOption) then closest=limOrDedOption(_n_);

end;

RUN;

Re: %Scan function error

MARY9999 — Mon, 20 Oct 2014 18:24:06 GMT

Ideally yes, i have t odo this for 20 different variables.

Re: %Scan function error

RW9 — Tue, 21 Oct 2014 08:34:55 GMT

I would agree with Arthur, arrays are easier for lots of variables as you can then apply the same logic across any number of variables, something like:

data have;
infile datalines dlm=',';
input var1 var2 var3 range1 range2 range3 range4;
datalines;
10,30,15,12,8,14,19
20,35,15,12,8,14,19
16,30,17,12,8,14,19
;
run;

data want;
set have;
array vars{3} 8. var1 var2 var3;
array range{4} 8. range1-range4;
array res{3} 8.;
do i=1 to 3;
    do j=2 to 4;
      if range{j-1} <= vars{i} <= range{j} then do;
        if (vars{i}-range{j-1}) / (range{j}-range{j-1}) < 0.5 then res{i}=range{j-1};
      end;
    end;
end;
run;

Re: %Scan function error

jh_ti_bw — Tue, 21 Oct 2014 15:45:29 GMT

there are different Errors.

the macro does not do, what is intended. The limOrDedSetFlag was not changed, in the loop, if the Operation was succesfull. So always writes the next loop over the result of the previous loop.
the Expression "&curLimOrDed. - 1" in the %scan-function must be "%eval(&curLimOrDed. - 1)"
The test is very complicated. Why do you not simply compare the differences from limorDedValue to lower bound and upper bound.

%MACRO FIND_CLOSEST_VALUE(limOrDedOption,limorDedValue);

%LET numOptions = %Sysfunc(Countw(&limOrDedOption.,%str( )));

limOrDedSetFlag = 'N';

/* probe of less or equal as the smallest value */

if &limOrDedValue. <= %Scan(&limOrDedOption., 1,%str( )) then do;

&limOrDedValue. = %Scan(&limOrDedOption., 1,%str( ));

limOrDedSetFlag = 'Y';

end;

/* probe of equal or greater as the highest value */

else if &limOrDedValue. >= %Scan(&limOrDedOption., -1,%str( )) then do;

&limOrDedValue. = %Scan(&limOrDedOption., -1,%str( ));

limOrDedSetFlag = 'Y';

end;

else do;

/* the value must be between 2 values or equal a value of the list*/

%DO curLimOrDed = 2 %TO &numOptions.;

IF limOrDedSetFlag = 'N' THEN DO;

lwrVal =%Scan(&limOrDedOption., %eval(&curLimOrDed. - 1));

uprVal =%Scan(&limOrDedOption., &curLimOrDed.);

IF (lwrval <= &limOrDedValue.) and (&limOrDedValue.) <= uprVal THEN DO;

IF (&limOrDedValue. - lwrVal) < (uprVal - &limOrDedValue.)

THEN &limOrDedValue. = lwrVal;

ELSE &limOrDedValue. = uprVal;

limOrDedSetFlag = 'Y';

END;

%END;

end;

%MEND;

/* usage */

%LET
numArray = 200000 300000 500000 1000000 2000000;

optionsmprint;

DATA TEST;

testvalue1= 34000;

testvalue2= 340000;

testvalue3=3400000;

%FIND_CLOSEST_VALUE(&numArray., testValue1);

%FIND_CLOSEST_VALUE(&numArray., testValue2);

%FIND_CLOSEST_VALUE(&numArray., testValue3);

RUN;

Re: %Scan function error

art297 — Tue, 21 Oct 2014 18:15:44 GMT

I still think that using arrays in a datastep would be your best solution but, based on the code offered by jh-ti-bw, here is one way that you could do it using a macro:

%macro FIND_CLOSEST_VALUE(limOrDedOption,limorDedValue);

%let numOptions = %Sysfunc(Countw(&limOrDedOption.,%str( )));

limOrDedSetFlag = 'N';

/* probe of less or equal as the smallest value */

if &limOrDedValue. <= %Scan(&limOrDedOption., 1,%str( )) then do;

lwrVal = %Scan(&limOrDedOption., 1,%str( ));

uprVal = %Scan(&limOrDedOption., 1,%str( ));

limOrDedSetFlag = 'Y';

end;

/* probe of equal or greater as the highest value */

else if &limOrDedValue. >= %Scan(&limOrDedOption., -1,%str( )) then do;

lwrVal = %Scan(&limOrDedOption., -1,%str( ));

uprVal = %Scan(&limOrDedOption., -1,%str( ));

limOrDedSetFlag = 'Y';

end;

else do;

/* the value must be between 2 values or equal a value of the list*/

%do curLimOrDed = 2 %to &numOptions.;

if limOrDedSetFlag = 'N' THEN DO;

lwrVal =%Scan(&limOrDedOption., %eval(&curLimOrDed. - 1));

uprVal =%Scan(&limOrDedOption., &curLimOrDed.);

IF (lwrval <= &limOrDedValue.) and (&limOrDedValue.) <= uprVal THEN

limOrDedSetFlag = 'Y';

end;

%end;

end;

if lwrVal eq uprVal then closest=lwrVal;

else do;

if &limOrDedValue.+(uprVal-lwrVal)/2 gt uprVal

then closest=uprVal;

else closest=lwrVal;

end;

drop lwrVal uprVal limOrDedSetFlag;

%mend FIND_CLOSEST_VALUE;

/* usage */

data test;

input testvalue1 testvalue2;

cards;

34000 20

249000 24

249999 25

250000 26

250001 30

251000 31

340000 35

1400000 40

2400000 44

;

data want;

set test;

%FIND_CLOSEST_VALUE(200000 300000 500000 1000000 2000000, testValue1);

closest1=closest;

%FIND_CLOSEST_VALUE(15 20 22 24 26 28 30 32 34 36 38 41, testValue2);

closest2=closest;

drop closest;

run;