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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ebad_shakeeb
Fluorite | Level 6

Thanks a lot dear.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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

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
  • 7 replies
  • 5132 views
  • 9 likes
  • 4 in conversation