Conditional SAS+SQL

I have a SAS JOB which i want to be a combination of two jobs based on a SQL condition.
More Clearly
SAS JOB STARTS
EXECUTES A SQL STATEMENT WHERE IT SELECTS a VALUE FROM A TABLE BASED ON A WHERE CONDITION
IF THAT SELECTED VALUE IS ZERO
   RUN SAS JOB 1
ELSE
  RUN SAS JOB 2

How do i do that ?
LVL 1
misermiceAsked:
Who is Participating?
 
d507201Database Marketing ConsultantCommented:
Oh yes, you can make this into a single program.  When you said Job1 and Job2 I presumed that you already had separate programs written that you wanted to run.  

In place of the %include you can have any combination of DATA steps and PROCs like any other SAS program.  Just cut and paste in your existing code.  (Debug it outside the macro--it will be easier.)  I often break long SAS programs into manageable pieces and then use %include to call them in the proper order.  

When I say fully-defined path, that's the directory and file where the SAS code is located.  For example, on a Unix server I might say
    %include '/sasusers/d507201/project/ru70/extract_customer_data_from_bmg.sas' / source2;

The source2 option echos the included code to the log.  Our default is nosource2 which supresses the code.

PROC SQL is a sort of interactive procedure.  It doesn't recognize the RUN boundary marker but QUIT; tells it when to stop.

If you run the PROC SQL that creates the macro variable holding the key value _outside_ the macro then it will be available to the non-macro parts of the program.  If you put it inside the macro then you will have to insert the %global statement before the PROC that creates the macro variable.

%macro chooseAndRun() ;
  %global value;
proc sql;
  select [field] into :value from [table] where [condition];
quit;
<more code>
0
 
d507201Database Marketing ConsultantCommented:
There are a couple of viable approaches to solve this problem.  For both, you need to somehow put what I'm calling _keyValue into a macro variable.

Myself, I'd take this a approach.  I use this sort of thing a lot when I need conditional execution.

%macro chooseAndRun();
%if &_keyValue=0 %then %do;
    %include 'fully-defined path to code for job 1';
%end;
%else %do;
    %include 'fully-defined path to code for job 2';
%end;
%mend;

The other approach would be to use CALL EXECUTE in a DATA step.  I don't use CALL EXECUTE, but there are quite a few SUGI and I think NESUG papers on it

data _null_;
  if "&_keyValue"="0' then call execute ('code for job 1');
  else call execute ('code for job 2');
run;

Good luck.

0
 
Aloysius LowCommented:
this part would do the checking of a value:
proc sql;
  select [field] into :value from [table] where [condition];
quit;

--> Note: value is a variable name. you can use anything else.

then like the answer above:
%macro chooseAndRun();
%if &value=0 %then %do;
    %include 'fully-defined path to code for job 1';
%end;
%else %do;
    %include 'fully-defined path to code for job 2';
%end;
%mend;
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
d507201Database Marketing ConsultantCommented:
Whoops, Iowaloysius is right... I neglected to say how to get the key value from the table.  I'd use that same code.  SELECT <field> INTO : <macro var> is very handy syntax to know.
0
 
misermiceAuthor Commented:
@507201  and @Iowaloysius

Thank You for your responses, my first question is when you say "'fully-defined path to code for job 1'"
or job 2. Do i need to keep the job1 as seperate SAS job ?
I was planning to make a single job. Cant i put the code in there that is the full SAS job similarly in place of 'fully-"defined path to code for job 2"
0
 
misermiceAuthor Commented:
Based on what you suggeste here is my sas file. I would really appreciate if you could point out any errors in there.

I used TRUNC with dates since to chop of the time part od date since its on oracle.

I was also wondering since we are saying    QUIT;   after the PROC SQL the , the value of key_value will be stiull carried to otther parts of the program that is whiel checking the conditions.
0
 
misermiceAuthor Commented:
here is my file. Please have a look
sasjob.txt
0
 
Aloysius LowCommented:
it'll be good if you could attach the log files as well and give a brief of the results.

looking at the code, i'm thinking you have problems at the proc sql step.
proc sql;
 SELECT Emp_Total INTO :key_value FROM EmployeeUpdate
        WHERE TRUNC(UpdateDate) = TRUNC(SYSDATE);
quit;

your selection is from EmployeeUpdate table. However, this being SAS, if there's no prefix of library, it is assumed that the table is coming from WORK. have you defined a library where the EmployeeUpdate table can be found?

Secondly, select :into is a SAS function. Hence, I believe TRUNC function, which is a SQL function will cause an error, unless you are explicitly using SQL pass through. try DATEPART function instead.

there's one more mistake that i spot, but i'm not sure if it's because you have accidentally removed it the ending quotation mark at the end of the line.
   x "sqlplus cron_app/a cron job that runs a SQL report on Oracle DB


If you could, it'll be extremely helpful if you could provide the log file so that it's easier to help.
0
 
Aloysius LowCommented:
and more one thing regarding the the SQL selection statement. other than using DATEPART function, change TRUNC(SYSDATE) into DATE()

this being SYSDATE is also and SQL keyword, and unless you are using explicit SQL pass through, it will also not work.
0
 
misermiceAuthor Commented:
As you said i made the suggested chnages. And i have a file by name autoexec.sas which has the LIBNAMES defined. To that file i added the schema from which i am accessing the current table. I already have three schemas in that file so i added it in a smiliar fashion.

HEre is how i added it

libname schemaname oracle
user=username
  password=refrence2encryptedpwd
path="DB Name"
preserve_col_names=yes
schema=schemaname;


Th eautoexec ran and said that libname with name snehmaname has been successfulyy assigned.

but still when i ran the sas job i go the following error

ERROR: Libname schemaname is not assigned.

what do i do now ?


here is the part of log after i ran sas job


NOTE: SAS initialization used:
      real time           0.11 seconds
      cpu time            0.11 seconds
     
1          
2          PROC SQL ;
3            SELECT Emp_Mgr_Total_Changes INTO :key_value FROM schemaname.EmployeeUpdateEmails
4                  WHERE DATEPART(UPDATEDATE)= DATE();
ERROR: Libname schemaname is not assigned.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
5          
6          QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     
7          
8          %MACRO TermsEmailJobs;
9          %if &key_value=0 %then %do;
10        
11         filename outbox email 'email address;



Please suggest
0
 
Aloysius LowCommented:
where and how are you running the code?

if triggering from base SAS or command line, then that answers your question. it's likely that you have ran the code this way, from the log you have provided - that the 1st line of the log shows that your code is being executed straightaway.

typically, when code is executed from the command line, we'll include a -autoexec option such that it gets triggered first before the actual code. if you are running from base SAS only, without any intention of running from command line (e.g. scheduling the job), then i suggest that you to add the following at the very beginning of your code to trigger the autoexec:
%include "[full file path and name of auotexe file].sas";
0
 
misermiceAuthor Commented:
I go to putty to get to the code in my directory on sas server.
I got to the sas files directory and in the bash mode i say
SAS mysasjob.sas
This is how it runs. Anyways i will try to include the autoexec file to lets see wat happens.

0
 
Aloysius LowCommented:
well in that case, do something like sas -sysin mysasjob.sas -autoexec [myautoexec.sas] and try again
0
 
misermiceAuthor Commented:
Hmmmm
My SAS job does this
Checks for a value in a oracle table and if that value is zero
sends an email with soem content
Else
sends an email by running a SQL Report and attaching it to email.

After i added the include statement for autoexec.sas
I threw No errors. Log says teh job successfully ran.
But i didnt get any email. coz no matter what the value of that ket_value is ..i shd get an email right?
0
 
Aloysius LowCommented:
well that would depend on your email server... have you managed to get it to send an email successfully before in the first place?

what's the log like?
0
 
d507201Database Marketing ConsultantCommented:
As I recall, libnames are limited to 8 characters.  Rerun your autoexec.sas and check the log section where the autoexec.sas runs.  I think you'll see that the libref schemaname was never assigned.
0
 
misermiceAuthor Commented:
@lowaloyisius
Yes i tried to execute both of teh sas jobs in seperate test blocks .
both of them send emails.

Now when i looked at the log it said

after the SQL statements it said this

'No rows selected'

but there are rows that can be selected i tested in SQL.

So i think the problem is with the customized we r writting for SAS iob

is there a way i cant test only that. ?

do u think there is  a problem with datepart or date or...value...
0
 
misermiceAuthor Commented:
@d507201

Thank You. S i faced that problem. i donno y it is limitted to 8 digits.
anyways i have to change the schema name.
i means used diferent schema aftr moving table into that schema.
0
 
Aloysius LowCommented:
what is the date value you are trying to extract?

for example, if it's 24th Aug 2010, try "24Aug2010"d in place of DATE(). does this return any result?

what is the type of UPDATEDATE in oracle? is it a date or date time? what is the data stored inside like? if it does not store a time component, or if the time value is all 00:00:00, then try to do without the DATEPART function.
0
 
misermiceAuthor Commented:
The updatedate in EMPLOYEEUPDATEDATE is like this 8/24/2010 12:45:26 PM
so it does makes sense to use DATEPART on UPDATEDATE.

By teh way is there a way to just test the SQL component of this job.
Can just run the PROCSQL and display key_value
please let me know how to display key_value.
That way we would know that atleast the SQL part works fine.
0
 
Aloysius LowCommented:
try replacing DATE() with "24Aug2010"d

try doing this:
PROC SQL ;
SELECT Emp_Mgr_Total_Changes INTO :key_value FROM [schemaname].EmployeeUpdateEmails
WHERE DATEPART(UPDATEDATE)= "24Aug2010"d;
QUIT;

%put &key_value;

you'll find the value stored in the variable key_value being output. however, if you mentioned that no rows are selected, then it makes sense, since no rows are selected, nothing gets put into key_value...

anyway, remember to change the library name schemaname to something less than or equals to 8 characters.

do provide the log as well
0
 
misermiceAuthor Commented:
When i do this DATEPART(UPDATEDATE)= "24Aug2010"d;

It works.. It is creaating a new file called sasjobfilename.LST and populated that file with the result.
So now the question is how do i replace "24Aug2010"d; witha dynamic function which returns TODAY's DATE.
0
 
Aloysius LowCommented:
date() should do the trick actually. but i'm just wondering when you were doing the earlier query, was there any data for that date...
0
 
misermiceAuthor Commented:
It works.
0
 
Aloysius LowCommented:
i have requested for a 50-25-25 split as follow:
50 for 33487829
25 for 33489896
25 for 33489905

reason: 33487829 answers most of the question, which is the currently accepted answer

upon implementing 33487829, the asker posted 33488292, which contained some errors in the code, to which i responded with 33489896 and 33489905 which the asker replied to say he has implemented the changes in 33503510.

finally, the asker mentioned that (arising from my advise in 33489896) that no rows are selected in 33513554 which i further responded in the following posts until he replied in 33668864 to say that it now works.

comments please.
0
 
thermoduricModeratorCommented:
I am starting the auto-close procedure to split the points among a number of Expert comments. This seems to be more equitable than the original disposition of the question.

- thermoduric -
EE Community Support Moderator
http://www.experts-exchange.com/Q_26723778.html

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.