DATA Step, Macro, Functions and more

Do while with Multi-Conditions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Do while with Multi-Conditions

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.


Accepted Solutions
Solution
‎05-19-2015 09:59 AM
Trusted Advisor
Posts: 1,932

Re: Do while with Multi-Conditions

Posted in reply to ebad_shakeeb

%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 ));

View solution in original post


All Replies
Solution
‎05-19-2015 09:59 AM
Trusted Advisor
Posts: 1,932

Re: Do while with Multi-Conditions

Posted in reply to ebad_shakeeb

%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 ));

Occasional Contributor
Posts: 5

Re: Do while with Multi-Conditions

Posted in reply to PaigeMiller

Thanks a lot dear.

Super User
Super User
Posts: 7,988

Re: Do while with Multi-Conditions

Posted in reply to ebad_shakeeb

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.

Super User
Posts: 7,854

Re: Do while with Multi-Conditions

Posted in reply to ebad_shakeeb

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Do while with Multi-Conditions

Posted in reply to KurtBremser

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);

Super User
Super User
Posts: 7,988

Re: Do while with Multi-Conditions

Posted in reply to ebad_shakeeb

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.

Occasional Contributor
Posts: 5

Re: Do while with Multi-Conditions

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 339 views
  • 9 likes
  • 4 in conversation