There is also the question as to how you transferred your mainframe file to Unix. You should only use EBCDIC informats if the file was transferred in BINARY mode so that the file is still EBCDIC-encoded. If the file was transferred as TEXT then the translation to ASCII would have happened automatically so that you don't require the EBCIDC informats. If your input file just looks like standard text in a Unix text editor its already in ASCII. ... View more

One possibility is to summarise and order your data first using the MEANS/SUMMARY and SORT procedures then define your ORDER and GROUP variables as DISPLAY instead. ... View more

I would be interested in knowing why your boss does not recommend using PASSTHRU SQL. The only negative I can think of is the syntax differences. If you are familiar with Oracle SQL that problem disappears. FYI, we use PASSTHRU SQL almost exclusively for complex queries as you can easily add Oracle specifics like HINTS that can dramatically improve performance (for example query parallelisation). And you can test your query in TOAD and then insert it unchanged into your SAS program and expect very similar performance. These are important issues for large, complex production processes that need to run as efficiently as possible. It also removes the issue of having to investigate how SAS is translating your SQL. I would agree however that SAS SQL and language is the way to go for simpler processes and smaller data volumes where ultimate processing efficiency is not required. ... View more

I suggest you try PASSTHRU SQL in SAS as this is the exact equivalent of a query in TOAD. Try this: proc sql; connect to oracle ( user = user password = password path = xxpath buffsize = 32767 preserve_comments ); create table example as select * from connection to oracle ( * put your TOAD Oracle query here ; ); disconnect from oracle; quit; A query via a SAS LIBNAME requires SAS to translate your query into PL/SQL and that may not be exactly the same as your TOAD version. ... View more

Are you aware that the official SAS line is there are no plans to support SAS 9.1.3 under Windows 7? I suggest it would be much easier to install SAS 9.2. Any maintenance release from Dec 2009 is officially supported. BTW, if you are running 64-bit Windows you need the 64-bit SAS installation media. ... View more

There is an item in the latest SASware Ballot in the Language category on this issue: "add an option on the INFILE statement that enables you to perform case-insensitve comparisons by including @'string' on the INPUT statement" If you are keen you should vote for it! ... View more

SAS/ACCESS to PC FILE FORMATS is available under Unix but you may not have it licenced and/or configured correctly. Run the SETINIT procedure to confirm if it is licensed or not. If not a quick fix may be to save the spreadsheet as a comma or tab delimited file and change your PROC IMPORT code accordingly. ... View more

Thanks for the tip Peter. WAITFOR is definitely the way to go to take back control after child parallel SAS processes have run. ... View more

I have successfully used SAS/CONNECT for this purpose. If you have this licensed then this could be an option. The trick is to use the WAIT = NO option when submitting the child jobs as this then allows the controlling SAS job to continue without waiting for the submitted child SAS job/session to finish. For example running two parallel jobs from a controlling SAS session: signon session1; signon session2; rsubmit session1 wait = no; %include "program1.sas"; endrsubmit; rsubmit session2 wait = no; %include "program2.sas"; endrsubmit; Please note that session1 and session2 could be defined as additional SAS sessions on the same computer you are working on or they could refer to a remote computer. The choice is yours. There is the problem of not knowing when the child sessions finish their work so you can continue on afterwards with more processing. This can be solved by getting the child sessions to write a special file or dataset when they end. Your controlling SAS session can periodically check for these special files and only continue after they are created. ... View more

While Linus is correct in that no one technique is necessarily more efficient than another, I stand by my comment that the MEANS procedure has a significant invocation overhead compared to SQL. Therefore simple summary stats in SQL like for example: proc sql; create table sum as mean(age), max(height from sashelp.class group by sex; can often be more efficient than the procedure. Please note this is only worth pursuing for large datasets where you have performance issues and you do have to benchmark your methods for ultimate proof. ... View more

My experience has been that SQL runs faster for deriving a few basic statistics like mean, sum, max with simple grouping, even if the code is longer. There is a significant overhead just invoking the MEANS procedure so for simple statistics and single level grouping it is usually slower. However MEANS is definitely a better choice for more complicated grouping and statistics. ... View more

There is the PREVIEW function that can include an external SAS program. Check the online help for the full details, but you will need to do something like this in your SCL: rc = filename(fref,'c:\MyFolder\MyProgram.sas'); rc = preview('include', fref); Then a submit continue step will execute the program: submit continue; endsubmit; PREVIEW also allows you to view the program prior to running it which can be useful. ... View more

The following example is closer to what you want: proc sql noprint; select memname into :mem_list separated by ' ' from dictionary.members where upcase(libname)='SASHELP' ; quit; %put &mem_list; You can limit the types of SAS files to search on by using MEMTYPE in the WHERE clause. ... View more

From personal experience the fastest way to get info on SAS files is to use the DICTIONARY tables directly in SQL. For example: proc sql noprint; select name into :var_name separated by ' ' from dictionary.columns where upcase(libname)='SASHELP' and upcase(memname)='CLASS'; quit; %put &var_name; This example builds a variable list for the selected table and stores it into a macro variable. It is a very handy technique that can be used for a variety of purposes. ... View more

Try this: proc sql; connect to odbc(user=xxx password=xxx datasrc=Oracle); create view rollup_vw as select year length = 4 ,code ,description from connection to odbc (SELECT T1.YEAR, T1.Code, T1.DESCRIPTION FROM MIDF.Organization T1 quit; ... View more