Locale names
Posted by Oleg Solovyev on Apr 26, 2011
Once at the interview they proposed me to show my programming skills on the production database. The interviewer rose from his table and asked me to write any code I want. I set down at his computer, opened the SAS window and wrote simple query that returns a list of all columns in the database:
proc sql; create table test as select * from dictionary.columns; quit;
“Well you rashly gave me access to your product DB and now I can get any information out of it.”
“Hey, be careful!”
“For instance I can get all the column names in your database.”
“Hm… Nice!”
DWH optimization
Posted by Oleg Solovyev on Apr 23, 2011
Indexes
The first thing one should start with is indexes. They decrease table read time if one of the columns in the where statement is indexed. They also decrease tables join/merge time if one of the ID columns is indexed. The list of DWH indexes is available in the system table DICTIONARY.INDEXES:
proc sql; create table work.indexes_list as select * from dictionary.indexes; quit;
Indexes can be simple and combined. Simple index is created on one column. Combined index involves several columns. The main difference is that combined index is more efficient then simple index when query involves filter or join based on several columns. SAS compiler decides what index to use depending on the query code and indexes available.
Airline data
Posted by Oleg Solovyev on Apr 13, 2011
US Bureau of Transportation Statistics not only collects data but provides access to it through its web site BTS.gov. For example SAS uses airline data in its advertising and education materials to demonstrate time series forecasting.
This time series is ideal for demonstration because it contains the trend (the average of 12 months is constantly increasing) and seasonality (every value “repeats” every 12 months). This article shows how to download this data and import it into SAS.
Excel Import
Posted by Oleg Solovyev on Apr 12, 2011
Recently I had to import Excel file into SAS DWH. There are three popular ways:
- import wizard: File &rarr& Import Data
- SAS library with ODBC engine assign to Excel file
- infile and input statements in the data step
All these methods work well when the data is in one table. E.g. first row contains variables names and the rest is data. But my Excel file had several blocks of data. I could transform them into separate spreadsheets but human factor increases errors. Anyway I would have to do the same thing any time new file comes.
SPDE library
Posted by Oleg Solovyev on Mar 14, 2011
SAS standard library is called V9. Each table in the library is a separate file on disk. SPDE is an alternative library. Data set in this library can be saved in several files to increase I/O operations or to store big tables on several discs. The cool thing is that SPDE doesn’t require additional license and comes with Base SAS.
In terms of Oracle or PostgreSQL the technology is called partitioning.
