Solved

Conditional SAS+SQL

Posted on 2010-08-19
28
1,057 Views
Last Modified: 2013-11-16
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 ?
0
Comment
Question by:misermice
  • 11
  • 10
  • 4
  • +1
28 Comments
 
LVL 7

Expert Comment

by:d507201
ID: 33478763
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33481098
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
 
LVL 7

Expert Comment

by:d507201
ID: 33485193
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
 
LVL 1

Author Comment

by:misermice
ID: 33487291
@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
 
LVL 1

Author Comment

by:misermice
ID: 33487438
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
 
LVL 7

Accepted Solution

by:
d507201 earned 300 total points
ID: 33487829
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
 
LVL 1

Author Comment

by:misermice
ID: 33488292
here is my file. Please have a look
sasjob.txt
0
 
LVL 14

Assisted Solution

by:Aloysius Low
Aloysius Low earned 200 total points
ID: 33489896
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
 
LVL 14

Assisted Solution

by:Aloysius Low
Aloysius Low earned 200 total points
ID: 33489905
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
 
LVL 1

Author Comment

by:misermice
ID: 33503510
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33506704
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
 
LVL 1

Author Comment

by:misermice
ID: 33511688
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33511733
well in that case, do something like sas -sysin mysasjob.sas -autoexec [myautoexec.sas] and try again
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:misermice
ID: 33511815
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33511836
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
 
LVL 7

Expert Comment

by:d507201
ID: 33511860
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
 
LVL 1

Author Comment

by:misermice
ID: 33513554
@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
 
LVL 1

Author Comment

by:misermice
ID: 33513568
@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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33516941
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
 
LVL 1

Author Comment

by:misermice
ID: 33522319
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33522386
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
 
LVL 1

Author Comment

by:misermice
ID: 33532828
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33537925
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
 
LVL 1

Author Comment

by:misermice
ID: 33668864
It works.
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 34506825
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
 

Expert Comment

by:thermoduric
ID: 34680423
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now