topic Re: Do while with Multi-Conditions in SAS Programming

Do while with Multi-Conditions

ebad_shakeeb — Tue, 19 May 2015 13:46:28 GMT

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.

Re: Do while with Multi-Conditions

PaigeMiller — Tue, 19 May 2015 13:59:04 GMT

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

Re: Do while with Multi-Conditions

RW9 — Tue, 19 May 2015 14:07:00 GMT

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.

Re: Do while with Multi-Conditions

ebad_shakeeb — Tue, 19 May 2015 15:00:55 GMT

Thanks a lot dear.

Re: Do while with Multi-Conditions

Kurt_Bremser — Tue, 19 May 2015 15:02:00 GMT

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.

Re: Do while with Multi-Conditions

ebad_shakeeb — Wed, 20 May 2015 04:55:35 GMT

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

Re: Do while with Multi-Conditions

RW9 — Wed, 20 May 2015 08:22:34 GMT

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.

Re: Do while with Multi-Conditions

ebad_shakeeb — Wed, 20 May 2015 09:34:21 GMT

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