In the link below it is possible to figure out what SAS-formats correspond different data types in Microsoft SQL-server.
Get CSV-file from SAS to Excel PowerPivot
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.
|
1 2 3 4 |
ods csv file="ODS_CSV.csv"; proc print data=sashelp.class; run; ods csv close; |
I have found that this will do the correct formatting of text in “ “.
Excel PowerPivot duplicate column names in source file
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.
Using MSSQL TEMPDB in SAS
You can use the TEMPDB in Microsoft SQL-server through SAS by creating a ODBC-libname – like the libname below.
|
1 |
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
|
1 2 3 |
data tmplib.'#temp'n; set sashelp.class; run; |
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.
|
1 2 3 4 5 6 |
proc sql; create table temp as select * from tmplib.'#temp'n ; quit; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
libname tmplib odbc noprompt="driver=odbc driver 11 for sql server; server=<servername>; database=tempdb; trusted_connection=yes" schema=dbo connection=shared dbmstemp=yes; data tmplib.<SASTEMP-tablename>; set <tablename>; run; 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 ( select a.* from <tablename> a inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable> ); disconnect from odbc; quit; |
Further information can be found in this link: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/
default/viewer.htm#a002677192.htm
Extending the Display Manager in SAS
The commands below can be used to expand the Display Manager in SAS.
| Command | Description |
| vt &syslast.; | 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. |
Error when doing a synchronization of SSAS
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.
Get port-number for MSSQL-server
To get the port number used by your MSSQL-server, you can use the command below.
|
1 2 3 4 5 6 7 |
SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID GO |
Quoted SQL IN-statement in SAS
Below is code that produces a quoted string from a column in a dataset. This can be used in an IN-statement in SQL.
|
1 2 3 4 5 |
proc sql; select quote(Compress(name)) into :SQLMetaSrc separated by ',' from datasets ; quit; |
SAS “behind the scenes”
Below is a description of very useful options in SAS, if you want a look “behind the scenes” and see what SAS actually does when processing data.
|
1 |
options fullstimer sastrace=(,,,d) sastraceloc=saslog mprint source2 nostsuffix; |
| fullstimer | The SAS System provides the FULLSTIMER option to collect performance statistics on each SAS step, and for the job as a whole and place them in the SAS log. It is important to note that the FULLSTIMER measures only give you a snapshot view of performance at the step and job level. |
| sastrace=(,,,d) | Generates trace information from a DBMS engine.
‘,,,d’ specifies that all SQL statements that are sent to the DBMS are sent to the log. Here are the applicable statements: |
| sastraceloc=saslog | Prints SASTRACE information to a specified location. In this case the log in SAS. |
| mprint | Specifies whether SAS statements generated by macro execution are traced for debugging. |
| source2 | Specifies whether SAS writes secondary source statements from included files to the SAS log.
SOURCE2 specifies to write to the SAS log secondary source statements from files that have been included by %INCLUDE statements. |
| nostsuffix | The NOSTSUFFIX system option suppresses printing or display of trailing SASTRACE information and makes the SASTRACE log easier to read. |
Saving temporary tables in DB2
In DB2 it’s possible to save temporary tables in the schema SESSION.