February 2015 - BI Practice

SAS ODBC Driver Installation

I want to access the sas data table outside sas software and in Microsoft Access where I can edit the records more conveniently.

The version of sas I have is 9.3 and I googled the sas ODBC driver and came across the following link.

http://support.sas.com/downloads/browse.htm?cat=40#tab:2:

Initially, I installed the “Aug 2014” version, but it is for SAS 9.4 and didn’t work.

I uninstalled the sas ODBC for 9.4 and then installed the “July 2011” version which didn’t work either because I have Win 7.

Then I had to uninstalled ODBC for 9.3 and download the “Dec 2011” version.

Hope this time it works but it doesn’t. 🙁

Got this error message.

When launching setup.exe for ODBC installation. Got the following error message.

While having no success in installing SAS ODBC 9.3, I found some good reference resource on the website.

http://www.sascommunity.org/planet/blog/category/sql/

With some investigation, I found the odbcad32.exe hiding in a winsxs folder.

C:\Windows\winsxs\x86_microsoft-windows-m..-odbc-administrator_31bf3856ad364e35_6.1.7600.16385_none_44263d819f0aa19e

This might came from one of the windows service patch installed previously, but it is not the same ODBC administrator when I run from the “Control Panel” > “Administrative Tools”, which point to a different odbcad32.exe under C:\Windows\System32.

Apparently, the SAS ODBC 9.4 has already been recognized in the “ODBC Data Source Administrator”. Under the “System DSN” tab, click “Add”.

Find the “SAS” driver at the bottom of the list and click “Finish”.

In the “SAS ODBC Driver Configuration” window, there are 3 tabs, “General”, “Servers” and “Libraries”. Notice the Server name is defined as “__5001” which is the default “Local (Single User)” and if you change the name to something else, it will cause error in connection. Click “Configure…” on the “Servers” tab.

On the “Local Options” setting, check if the Path for sas.exe is correct and don’t change the default setting for “Startup parameters”. Click “OK” to go back.

Click “Library” tab and under the “Library Setting” give a Name for the library which holds the SAS data file you want to access through ODBC. In the “Host File”, provide the directory of the folder that contains the SAS data files. You can create many libraries for different SAS folders. Click “Add” to add the library and then “OK” to go back.

This completes the configuration of the “SAS ODBC Driver”.

I. A.

Renew SAS Software

Come to renew my SAS license, I was given a txt file SAS93_xxxxxx_xxxxxxxx_win_X64_Wrkstn.txt.

Click “Start” > “Program”

A dialog box pops up where you can input the txt file name with the directory.

The renew license information has to match the operating system, if not, the new license will not be able to apply and change the site validation data.

For example, my computer is a ‘w32_7pro’ and the renew license’s OSNAME specifies ‘wx64-wks’, and I got the following error message.

It will generate a setimit.log (c:\program Files\sashome\x86\SASfoundation) with the detail information.

If the license file is right and you browse to the folder where it is saved.

Click “Next” and a dialog box will show the SAS installation data being verified with all the dates renewed to the new dates.

Then you need to identify the folder where SAS is installed. On my computer it is c:\Program Files\SASHome\x86\SASfoundation\9.3., then click “Renew”.

You will get a pop up that the setinit is successfully installed.

I.A.

SAS output to date stamped Excel file

Sometimes people were asking for data and you just provided it to them. If it is an annual completed static data, you don’t have to do the date stamp to keep a record on when you retrieve the data from the system. However, if it is an operational ongoing data with day-to-day changes and also the data is not officially published for the year, you might want to keep a record of the date for reference.

The SAS code here basically provides a date format that you want to show on the Excel file name. The format code itself is quite complicated, but you don’t have to remember it at all. Just copy and paste. There are many variations to it. Then use macro functions %unquote and %sysfunc to get date and time as part of the file name.

proc format;
picture mydtfmt
low-high = ‘%Y_%0m_%0d_%0I%0M%p’ (datatype=datetime);
run;

PROC EXPORT DATA= Section
OUTFILE= “C:\Documents\My SAS Files(32)\9.3\Exce\section_%unquote(%sysfunc(datetime(),mydtfmt.)).xlsx”
DBMS=EXCEL REPLACE;
SHEET=”section”;
RUN;

Output:

In Windows Explorer,

Design Analysis Using Oracle BI- Basic work case 1

Develop an admission report.

1. On the “Home” tab, click “Edit” Grad Weekly Report.

2. In “Edit” mode, click “Criteria” tab.

3. Drag or double click the fields needed for the analysis from the left “Subject Areas” pane to add to the top “Selected Columns” pane.

4. Once added, drag-and-drop columns to reorder them.

5.Apply sorting on field. Please note if you already have sorted on other field(s), you need to use the “Add Ascending Sort” or “Add Descending Sort”, so that the second or more sort fields can be added (notice the sort number added next to the sorting symbol), otherwise the sort field will replace the previous sort setting.

6. Add Filters to the Analysis. Note the logical operator between the filters are “AND”, and you can click “AND” to change it to “OR”. Also pay attention to the indentation of the Filter criteria which is an indication of the grouping of the filters.

7. Save Analysis.

8. Click “Result” tab to modify layout of report results. The preliminary report is showing in the “Compound Layout” pane. Click the pen button to edit it.

9. Develop a pivot table design (most important report design). The fields selected previously on the “Selected Columns” under the “Criteria” tab are automatically plugged in the layout. You will need to make adjustments by dragging and dropping the fields to get the desired results. As you make the changes in the “Layout” pane, you will see the results on the top pane above the “Layout” pane.

9.1 Pivot Table Prompts. It gives you a report level filter for the end user to control.

Layout:

Results:

9.2. Section. It split the pivot table into sections by the value of the field selected. In the example, there will be a section for Masters programs and a section for PhD programs.

Layout:

Results:

9.3 Pivot Table Columns. Notice the “Measure Label” is on top of “Academic Year”, which means the “Academic Year” is a sub-column heading for each measurements. If the “Academic Year” is on top of the “Measure label”, then the column data for 2014 will be grouped together and 2015 data will be grouped together.

Layout:

Results:

9.4 Pivot Table Rows. For the row fields you can select to add subtotals for the measures. You can add the subtotal for the rows for the value grouping for each field (Notice the green ticket on the AutoSum symbol).

Layout:

Results:

9.5 Pivot Table Measures. These are the fields that you want to show the value and maybe also apply some calculation to show the variance or percentage change.

Layout:

Results:

9.6 Pivot Table Calculated field. I need a field to show the difference between the 2014 number and 2015 number. Because it applies to all the columns, the calculated field can be easily set up in “Columns”. “Academic Year”

Edit Calculated Item: I gave the label “Var.” to the calculated field. The formula is ‘2015’-‘2014’. We will talk about programing for dynamic calculated field in later topics. The result is shown as the illustration above.

Click save button and you just developed a basic report using Oracle Business Intelligence.

I. A.