Do while with Multi-Conditions

[ebad_shakeeb]

Dears,

I am new with SAS Macro, i have created a basic procedure in oracle, and need to have it in SAS as well,

Oracle Procedure,

Declare

      w1 number := 0;

      w2 number := 0;

      w3 number := 0;

      w4 number := 0;

BEGIN     

     LOOP

                       update io_campaign a set a.n_type='T' where rowid in (

            select rowid from io_campaign b where b.n_type='T' and rownum <=5 order by DBMS_RANDOM.RANDOM);

                    COMMIT;

                    update io_campaign a set a.n_type='C' where rowid in(

                    select rowid from io_campaign a where a.n_type='T' rownum <=5  order by DBMS_RANDOM.RANDOM);

                    commit;

             select a.TOTAL_W1, a.TOTAL_W2, a.TOTAL_W3, a.TOTAL_W4

            into w1, w2, w3, w4

            from precision_factor a where a.campaign_id='CAMP1539' and A.N_TYPE='PRECISION_FACTOR' AND A.EXEC_DATE=TRUNC(SYSDATE);

   EXIT WHEN W1 between 0.97 AND 1.03

         and W4 between 0.97 AND 1.03

         and W2 between 0.97 AND 1.03

         and W3 between 0.97 AND 1.03 ;

END LOOP;

END;

In SAS I have it like below and it is not working

%Macro abc;

libname mkt oracle path=mkt SCHEMA=XXXXX USER=XXXXX PASSWORD="XXXXXX";

proc sql;

%let w1 = 1.5;

%let w2 = 0.96;

%let w3 = 1.23;

%let w4 = 0.34;

%let a = 1.03;

%let b = 0.97;

select a.TOTAL_W1, a.TOTAL_W2, a.TOTAL_W3, a.TOTAL_W4

            into :w1, :w2, :w3, :w4

            from mkt.precision_factor a where a.campaign_id="CAMP1545" and A.N_TYPE='PRECISION_FACTOR' AND datepart(A.EXEC_DATE)=today();

%DO %WHILE((&w1 between '&b' and '&a' ) and (&w2 between '&b' and '&a' ) and (&w3 between '&b' and '&a' ) and (&w4 between '&b' and '&a' ));

update mkt.io_campaign set n_type='T' where subs_id in (

select x.subs_id from

(select a.subs_id,ranuni(count(*)) as nn from mkt.io_campaign a

     where a.campaign_id="CAMP1545" and a.n_type='C' and datepart(exec_Date)=today()-3 and monotonic() le 7)x);

update mkt.io_campaign set n_type='C' where subs_id in (

select b.subs_id from

    (select a.subs_id,ranuni(count(*)) as nn from mkt.io_campaign a

     where a.campaign_id="CAMP1545" and a.n_type='T' and datepart(exec_Date)=today()-3 and monotonic() le 7)b);

select a.TOTAL_W1, a.TOTAL_W2, a.TOTAL_W3, a.TOTAL_W4

            into :w1, :w2, :w3, :w4

            from mkt.precision_factor a where a.campaign_id="CAMP1545" and A.N_TYPE='PRECISION_FACTOR' AND datepart(A.EXEC_DATE)=today();

%put &w1 &w2 &w3 &w4;

%END;

run;

%MEND abc;

and receiving following ERROR

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

       operand is required. The condition was: (&w1 between '&b' and '&a' ) and(&w2 between

       '&b' and '&a' ) and (&w3 between '&b' and '&a' ) and (&w4 between '&b' and '&a' )

ERROR: The condition in the %DO %WHILE loop, (&w1 between '&b' and '&a' ) and(&w2 between '&b'

       and '&a' ) and (&w3 between '&b' and '&a' ) and(&w4 between '&b' and '&a' ), yielded an

       invalid or missing value, .  The macro will stop executing.

ERROR: The macro ABC will stop executing.

[PaigeMiller · Accepted solution]

%DO %WHILE((&w1 between '&b' and '&a' ) and (&w2 between '&b' and '&a' ) and (&w3 between '&b' and '&a' ) and (&w4 between '&b' and '&a' ));

Macro variable evaluation doesn't use quotes around macro variables.

I don't think the between operator can be used in this case

I think you need

%DO %WHILE(%sysevalf(&w1>=&b and &w1<=&a ) and %sysevalf(&w2>=&b and &w2<=&a ) and %sysevalf(&w3>=&b and &w3<=&a ) and %sysevalf(&w4>=&b and &w4<=&a ));

[ebad_shakeeb]

Thanks a lot dear.

[RW9]

Are you sure you want to be updating a database directly from SAS?  Just think if your macro resolves to something unexpected, you cold lose the whole database, or worse.  Perhaps setup a process on the database, and call that from SAS with parameters if really needed, otherwise just do the task on the DB, it will be quicker as well.

[Kurt_Bremser]

Since

select a.TOTAL_W1, a.TOTAL_W2, a.TOTAL_W3, a.TOTAL_W4

            into :w1, :w2, :w3, :w4

            from mkt.precision_factor a where a.campaign_id="CAMP1545" and A.N_TYPE='PRECISION_FACTOR' AND datepart(A.EXEC_DATE)=today();

will always render the same result, your %while loop would either execute never or indefinitely.

Unless some other process makes a change to the mkt.precision_factor table.

[ebad_shakeeb]

Thanks Dears,

For select 10 random numbers through SAS, i couldn't find so i have create a view in Oracle and call the data of that view in SAS.

ORACLE, for every iteration it passes the 10 random rows.

select * from

(select * from io_campaign a where a.campaign_id='CAMP1545' and a.n_type='CONTROL' and a.exec_date=trunc(sysdate)  order by DBMS_RANDOM.RANDOM) k where rownum <=10;

SAS, ranuni function it assign a value at once so the loop was continuously process, with a warning.

WARNING: A value expression of the SET clause references the data set being updated.

select x.subs_id from

(select a.subs_id,ranuni(count(*)) as nn from mkt.io_campaign a

     where a.campaign_id="CAMP1545" and a.n_type='CONTROL' and datepart(exec_Date)=today()-3 and monotonic() le 7)x);

[RW9]

I think you might be trying to do too much DB side work in SAS or vice versa there, especially if you don't know what your doing.  Firstly, focus on either programming in the database, or in SAS, don't try to shoehorn one into the other.  If you want to work in SAS, then one step proc sql create table to your local SAS, then use Base SAS to process the data further (rownumber for instance in SAS is _n_).  Be careful with the use of monotonic(), it may not assign the rownumber you expect as it is affected by parallel processing, and other clauses.  There are many different ways to get random observations:

For example, do a search on the communities, many posts.

I wouldn't recommend using observation order for your selection though, sorting, new data etc. will change it.

[ebad_shakeeb]

Dear RW9,

I am now with SAS, and it may take time to be deep in, and as well as the source data in two different DBs (Oracle and Teradata) so somehow i really need to get my result, currently I get the information from Teradata through Customer Intelligence studio and insert the information in Oracle at the same time it should be updated...