Below SAS-code shows how to make a libname for Oracle directly in SAS-code by providing information about the Oracle server, that you want to access.
libname <YOUR CHOICE> oracle user=<USERNAME> pw=<PASSWORD> Schema=<SCHEMA ON ORACLE>
(ADDRESS = (PROTOCOL = TCP)(HOST = <NAME/IP-ADRESSE OF ORACLE SERVER)(PORT = <PORT FOR ORACLE SERVER. DEFAULT ORACLE PORT IS 1539))
(SERVER = DEDICATED)
(SERVICE_NAME = <DATABASE ON ORACLE SERVER>))
The commands below will scan all *.sas files and look for the text proc. It will of course be possible to use any other string instead of proc. This scenario can be used to scan all your SAS-programs for the usage of SAS-procedures. Be aware that you need to be administrator or run the command as administrator to have access to all folders.
It will scan from the root of a server and go recursively through all the subfolders it encounters. It will pipe the result of the scan to the file linuxsasproc.txt in the folder where you start the execution of the command – this can of course be changed if you want to.
grep –include=\*.sas -rinw ‘/’ -e ‘proc‘ > linuxsasproc.txt
Output in linuxsasproc.txt. The file is delimited by : (colon)
<Location of SAS-program containing the search string>:<Line number in the file where the search string is found>:<The line that includes the search string>
It will scan through all subfolder from the location where you start the program. It will pipe the result of the scan to the file winsasproc.txt in the folder where you start the execution of the command – this can of course be changed if you want to.
findstr /s /i proc *.sas > winsasproc.txt
Output in winssasproc.txt. The file is delimited by : (colon)
<Location of SAS-program containing the search string>:<The line that includes the search string>
The command TNSPING can be used to get information about an alias for an Oracle server. Below you can see a screenshot from the command being executed in the command prompt for Windows.
In the command prompt you write: tnsping <the alias of your Oracle server>
TNSPING will return the
||Showing you the name of the physical host of the Oracle server.
||Showing you the port of the Oracle server on the physical host.
1521 is the default port for an Oracle server.
MediaHuman has made a really great product for downloading MP3 from YouTube. It can be found here: https://www.mediahuman.com/youtube-to-mp3-converter/
In the link below it is possible to figure out what SAS-formats correspond different data types in Microsoft SQL-server.
To get the correct formatting of a CSV-file from SAS to import into Excel PowerPivot, it’s possible to use the CSV ODS-tagset (https://documentation.sas.com/?docsetId=odsug&docsetTarget=n0jrwo0xyh8nlqn19u6uvrgx63gc.htm&docsetVersion=9.4&locale=en) and do a PROC PRINT of the dataset into an CSV-file.
ods csv file="ODS_CSV.csv";
proc print data=sashelp.class;
ods csv close;
I have found that this will do the correct formatting of text in “ “.
When importing for example a CSV-file in Excel PowerPivot you can be presented for the error below.
The expression contains invalid date constant ‘#csv.<VARIABLENAME>’.
If that’s the case you might have duplicate column names in the first column of the CSV-file.
You can use the TEMPDB in Microsoft SQL-server through SAS by creating a ODBC-libname – like the libname below.
libname TMPLIB ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<SERVERNAME>; DATABASE=TEMPDB; TRUSTED_CONNECTION=yes" schema=DBO CONNECTION=SHARED;
It’s important to provide the option CONNECTION=SHARED or else it will not work.
Through the libname it’s now possible to write and read from TEMPDB. The dataset has to have this syntax ‘#<DATASETNAME>’n e.g. like below ‘#temp’n
Be aware, that you are not able to view this new table through the Display Manager in SAS. When Microsoft SQL-server names the table, it makes the table name longer than SAS is able to display in the Display Manager.
You’re able to verify that the table do exist through SSMS (SQL Server Management Studio), or you can verify it’s existence by reading it back to SAS by using the code below.
create table temp as
If you want to use MSSQL-server temp-tables in Pass-Through SQL in SAS, then you need to use the libname option dbmstemp=yes.
Using this option will make it possible to execute the code below and force SAS to use the MSSQL-server to process the SQL-code in the pass-through SQL. If the option dbmstemp=yes is not used, then SAS will pull the data from the MSSQL-server back to be executed locally on the SAS-installation. It works with the below driver.
libname tmplib odbc noprompt="driver=odbc driver 11 for sql server; server=<servername>; database=tempdb; trusted_connection=yes" schema=dbo connection=shared dbmstemp=yes;
proc sql noprint;
connect to odbc (noprompt="driver=odbc driver 11 for sql server; server=<servername>; trusted_connection=yes");
create table <tablename> as
select * from connection to odbc
from <tablename> a
inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable>
disconnect from odbc;
Further information can be found in this link: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/
The commands below can be used to expand the Display Manager in SAS.
||Add to keys, e.g. F5. This opens a Viewtable with the latest run dataset/view.
|next viewtable:; end;
||Add to keys, e.g. F9. Then F9 closes the last used table (that is open). Can close all views with multiple F9’s.
|odsresults; select all; clear; wpgm;
||Add to keys, e.g. SHIFT F1. This key bind clears all SAS “results” and returns to the program editor.
|log; clear; wpgm;
||Add to keys, e.g. F4. Clears log without having to highlight the log window. Returns to the program editor.
If get the error
“An error was encountered in the transport layer”
when you try to synchronize an existing SSAS-database onto another empty SSAS instance for the first time, a solution could be to take a backup of the source SSAS-database and restore it onto the new SSAS instance and then do the synchronization.