Tag: sql

Convert CHAR to NUM in PROC SQL

Use TO_NUMBER function in PROC SQL. proc sql;   connect to oracle (user=xxx orapw=yyy path=”@zzz”);     create table temp as       select * from connection to oracle    &nb…

Building an SQL subquery in SAS Enterprise Guide

Many SAS Enterprise Guide users practically live in the Query Builder. For those who understand their data tables, the Query Builder provides a tremendous amount of flexibility to pull and manipulate data. The Query Builder produces SQL programs behind the scenes, which translates well for database-centric work. Sometimes a complex […]

LearningR!

I spent almost all of my blogging time last month to follow an online course in Coursera, Computing for Data Analysis (with R) by Dr. Roger  Peng of Johns Hopkins, Biostatistics Department. I already checked out bunch of Coursera courses just to take a look at what else MOOC look like, this R course was […]

A database professional’s best friend

To optimize a Structured Query Language (SQL), the database professional must befriend this order and perhaps even embrace it. Who is your best friend? I’m talking about the order in which SQL processes your statements. Simply put, in what order does…

How to Jump into SAS Data Integration Studio

This is for for SAS programmers who might be reluctant to check out a GUI tool like SAS Data Integration Studio (DIS for short, which is graphic tool to implement ETL processes: extract, transform, load). DIS translates all users dragged nodes, transformations and process into SAS codes which are traditionally written by SAS programmers. I […]

How to Get Row Numbers in SAS Proc SQL (and DO NOT Use the Undocumented MONOTONIC Function)

SAS programmers are longing for row number function used in Proc SQL, like ROW_NUMBER() in Oracle SQL and it will act like data step system variable _N_. When you google this question, most likely you will get MONOTONIC() function, which might be one of the most famous undocumented features shipped by SAS. You can of […]

NOTE: ODBC Performance

SAS is great at getting data from a huge variety of sources. With its SAS/ACCESS connectors, SAS can get data from these sources in an optimised fashion, i.e. using the native capabilities of the source data provider. In these days of increasingly larg…