Hello all you very smart SAS programmers.  I have a question for you about processing of SQL statements on an Oracle database.  Here is the basics of what I am experiencing.   I am use an explicit pass through to an Oracle database via proc sql.  I wrote a SQL statement that returned roughly 2.1 million records with a run time of about 2 hours.  I realized that I was returning more records then I needed so the only change I made to the code was to add a UNIQUE to the select statement.  This returned about 150,000 records and increased the run time to about 10 hours.  I was in shock!  How could a more restrictive query that is designed to return fewer records run loner and not just longer but five times as long.   The obvious answer to me was that there was some sort of system or connectivity issue so I repeated the experiment.  It has turned out to be very consistent.  My first thought was that I was being a silly little programmer and that I was breaking the pass through by using SAS specific syntax and that to process the unique statement data had to be returned from the native server to the SAS server I am running on.  But since I am using an explicit pass through, the statements would not compile or execute if that was the case.   The second thought I had was that UNIQUE is handled by Oracle in some fashion that I didn't anticipate.  I did some research on UNIQUE versus DISTINCT hoping to revealed how SQL handles those statements.  All I can determine is that the two are synonymous and should return the same results.  Nothing about processing.  As an experiment, I am going to re-run using a DISTINCT in place of the UNIQUE.   The final thought that I had was in regards to indexes.  I know the Oracle table I am pulling from is indexed and I have two WHERE clauses that reference those indexes.  One explanation for the increased run times would be that the UNIQUE statement creates a processing situation whereby the indexes are ignored.  If this is the case, I can accomplish the same thing as the UNIQUE by using a GROUP BY clause which should then decrease my run time from the 2 hour mark.  I am also going to try this as an experiment.   So, after all of that, I'd like to understand better how the UNIQUE or DISTINCT statements are handled by Oracle so that I can predict when using that code is an acceptable solution or when it should be avoided.  Does anyone know how the processing works or have any resources they can point me to where I can learn for myself? ... View more

So I was asked to look at a program that was using some macros but they are getting funny results.  It looks like some funny business going on with the original programmer so please bear with the story.   There was a process in place where a SAS program was run in EG.  When the program was submitted, 14 prompts were presented for the user to enter.  This was a pain.  A new programmer suggested that these prompts could be populated via a file instead and wrote some code to fill the 14 global variables from a file rather than from the prompts.  The program that the person doing the work runs originally only contained three lines of codes.  One option statement to set the location of the compiled macros and two statements that call the compiled macros.  The new code contains a file read in, a _null_ data step that creates the global variables, and the exact same three lines of code from the original program.   Here is where it starts to get weird.  When executing the two sets of code everything is the same except that in the new code one of the global variables gets blanked out.  Originally I thought we had a read in problem or that the symput wasn't working correctly.  However, I ran the pieces through and the blanked out global variable is populated until the second compiled macro is called.  After the macro finishes, the global variable is blanked out.  So I thought "ah-ha", I'll just look at the log and see what is going on.  I set option mprint and run the code.  Guess what?  I don't see any of the submitted statements from the compiled code in the log.  The only determination I can make is that the guy who wrote the code has an option nomprint as the very first line.  Because it is a compiled macro and we don't have access to the source code.  The guy who wrote the original thinks the new code is "less secure" and doesn't want the process to change.  He already sabotaged the new process twice but the new guy was able to get around those issues (with my help for one of them which is how I got involved).   That got me to thinking that the two programs were calling from different libraries.  I traced that back and no, the same macros are called in both programs.  That means that the same set of code is giving two different results.  The only difference is in the prompting.  What I can't figure out is how the macro knows if the global variables are populated from a file or a prompt.  Any thoughts or ideas on further analysis?   Using SAS EG 7.12 (I think) and SAS 9.4 if you think it matters. ... View more

I am using a libname statement to access an excel file.  In the past, we have found that when writing out to an excel file using this methodology the data is not always replaced.  To avoid that problem, we use a proc datasets and then delete the dataset before trying to replace it.  In this particular program, I have five different datasets that I am writing out to the excel file.  Three of them are cleared with the delete statement, two are not.  The two that are not are large (about 96,000 and 19,000 records).  The following is the code I am running: LIBNAME WrkBk EXCEL 'network location\Output Excel File.xlsm'; PROC DATASETS LIB=WrkBk;       DELETE BigData1; RUN; QUIT; Data WrkBk.BigData1;       set work.BigData1Ready; RUN; LIBNAME WrkBk CLEAR; We are using Excel 2010.  SAS 9.2 TS Level 2M3.  After the delete statement executes, the "dataset" BigData1 no longer is in the list of datasets.  However, if the excel file is accessed before the data step that populates the excel file is executed, the data is still there.  The three smaller tables all clear themselves out as I expect. Any assistance will be greatly appreciated.  Currently we are manually deleting the data before we run the code. ... View more

I'm getting the following error while trying access a SQL table trough an ODBC connection: Name 'THISSQLTABLEISREALLYLONGOVER32CHAR' is too long for a SAS name in this context. SAS only allows table names to be 32 characters but this one is longer then that. What can I do to be able to access the table? And no, I can't change the table name or run a query to duplicate the data to a table with a shorter name. ... View more