Category Archives: SAS

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.

I have found that this will do the correct formatting of text in “ “.

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.

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.

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.

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.

 

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.

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.

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:
SELECT
DELETE
CREATE
SYSTEM
CATALOG
DROP
COMMIT
INSERT
ROLLBACK
UPDATE
For engines that do not generate SQL statements, API calls and all parameters are sent to the log.

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.

SQL-statement to find lowest observation in dataset

The SAS SQL-statement below will create a dataset containing all information for the observation with the lowest age in the dataset SASHELP.CLASS.

The syntax for the SAS SQL-statement is showed below.

As you see the <FUNCTION> doesn’t have to be min (minimum), it can be any function working on the type of <COLUMN TO SEARCH> – numeric or char.

Info about SAS-datasets in the WORK-library

In SAS Enterprise Guide it is not very easy to see the size and number of observations in datasets in the WORK-library.

The macro below looks in the DICTIONARY.TABLES and gets these info for the WORK-library. Be aware that it will not work for views, because it’s not doing and actual count of the SAS-datasets.

 

Microsoft Team Foundation Server (TFS) and SAS

Below is shown how you can extract the current Microsoft Team Foundation Server (TFS) revision number for a given file into SAS and use it in your SAS program.

Be aware that Visual Studio 13 is used in the example below. It is uncertain if newer versions of Visual Studio will work.

You can now use the three diffent macrovariables called tfsver1, tfsver2 and tfsver3 in your program.

Microsoft Visual Studio Team Foundation Server 2013 Power Tools might be needed.
https://marketplace.visualstudio.com/items?itemName=TFSPowerToolsTeam.MicrosoftVisualStudioTeamFoundationServer2013Power

You might also want to have a look at this blogpost regarding SVN.