The handling of reading xlsx was improved a lot in SAS 9.4 when  xlsx engine was introduced.  Do you really still have 9.3?       ... View more

If you have access to a database like Oracle, then I recommend the use of database sequences to ensure you keep sequences clean. This is is especially useful is the numbers being sequenced are id columns and the tables may get records added by multiple program which may potentially run in parallel.   The database guarantees that no numbers are duplicated - which can happen if concurrently executing SAS programs all get the max value from a table and deduce the next number. I recently created a sas macro that uses Oracle passthru to initialise such Oracle sequences from the current maximum value (+ 1) This use Oracle passthrough. Once the sequences are established another macro gets the next sequence val. The heart of the macro has code like: proc sql noprint; connect to Oracle (&connection_info); /* not shown here: code to check if the sequence exists and create it if needed */ select nextval into :_netxval from connection to Oracle (select &sequence..nextval from dual); disconnect from Oracle;   The performance of identifying the maximum value depends on the number of records in the table.   Oracle sequences (and I have done the same in DB2) keep an underlying table with the sequence name and next value.  Queries to it locks the row, provides the next value, and then  unlocks the row, thereby guaranteeing uniqueness.   Oracle always does this in a fraction of a second. ... View more

Sorry but your reply does not help.   Run following code   proc setinit; run; Check that there is a line on the log like SAS/ACCESS Interface to Oracle  or AS/ACCESS Interface to ODBC. If that is not the case, then your site has not licenced the appropriate SAS product.   If it is the case, then you need to contact the SAS administrator / Oracle administrator to provide you with connection information   ... View more

Your organisation needs to have licenced SAS/ACCESS product.  The SAS administrators at your site should be able to provide you the connection  details. ... View more

Left Join creates a cartesian product of all records in the left table and matches each record in the right table that have a matching on condition. So if the right table has 3 records that match the condition, the result for the the row in the left table will be 3 rows. If you have a condition that does not single row on the left that matches the condition, then then that will match with all rows in right table that match the condition. For example, if you left join sashelp.class with itself on name you will not get duplicates. But if you join on age, then every 12 yera will be matched with very other 12 year old. So if you have 4 12-year olds, then the result will have 16 (4x4) 12-year olds. ... View more

How many records are in the input table, and how many are in the output table? Showing the log of this excerpt, plus that of the previous proc sort of AY would help a lot. ... View more

Look into hashing. The most common reason that sorts are done in SAS is to merge in following steps. If you use hashing, then you dont need to sort either table. Hashing is really useful when working with huge tables and you need to look up things in other "smaller" tables. The proviso is that the smaller table need to comfortably fit in memory. In an optimization consulting project where a table of 100 million records was had to be summarised after being enriched from multiple other sources, I managed to avoid all sorts and the large table was only parsed. The final output was a table with only 20,000 records and maybe 20 columns. The summary was done into a hash table and all enrichment was done in the same step. It was possible to reduce a run time of 1 hour into 1 minute. But this may not fit your problem: indeed, maybe you could simply adjust the memsize and sortsize options to get a huge boost in performance, without changing any code. ... View more