Format SQL Statement to look nice
Tranpose data efficiently in Netezza/SAS/SQL
Previously we have a few tutorials: how to Trun around
& Transpose data
nicely in SAS, by using array and proc transpose in SAS
(Also a tutorial for data tranpose in R
how to format SQL to look nice)
. You may realize that if you have millions of records to tranpose,
then it may take a while by using proc tranpose, especially when the number of columns after transposing is really big.
In this tutorial, we introduce some cool features of SQL to transpose data nicely and quickly.
CREATE TABLE car1a AS
SELECT DISTINCT DriveTrain,Origin,count(*) AS count
GROUP BY DriveTrain,Origin
ORDER BY DriveTrain,Origin;
Proc transpose data = car1a out = car1b;
*you can get better same OUTPUT BY using the following SQL **;
CREATE TABLE car2a AS
SELECT DISTINCT DriveTrain,
count(DISTINCT (CASE WHEN Origin = 'Asia' THEN make END )) AS Asia_distinct_make_count,
count(CASE WHEN Origin = 'Asia' THEN Origin END) AS Asia_count,
count(CASE WHEN Origin = 'USA' THEN Origin END) AS USA_count,
avg(CASE WHEN Origin = 'Asia' THEN Weight END) AS Avg_Asia_weight format 8.1,
avg(CASE WHEN Origin = 'USA' THEN Weight END) AS Avg_USA_weight format 8.1,
max(CASE WHEN Origin = 'Asia' THEN Weight END) AS Max_Asia_weight,
sum(CASE WHEN Origin = 'Asia' THEN MPG_City END) AS Sum_Asia_MPG,
GROUP BY DriveTrain
ORDER BY DriveTrain;
If it's inside Netezza/SAS, we can use the following to do tranpose inside netezza SQL.
create table Data1 as
select Var, count(*) as cnt
group by Var
Then print that table into excel, use
="sum(nvl(case when "&A1&"='"&A2&"' then Var_ID end,0)) as " &A2&" ,"
to generate the code needed for the following:
(It's a little bit tricky here, or use max if not numericals)
proc sql stimer;
connect to netezza (server='server_name' database=database1
user=myuser pwd=mypassword connection=global);
execute (create table outputfile as
sum(nvl(case when Option='ABS' then 12345 end,0)) as ABS ,
group by 1
distribute on random )
by netezza; quit;
Formatting SQL Statement
SQL statement is so useful that not only analysts are necessarily to get familiar with, also many IT stuff or database adminstrators are using that quite often.
The question is, sometime you get a very long SQL query, and it seems not that readable; Just like the following SQL query, it's really not that clear at the first glance of it.
The good news is that, we have some good tools to format the SQL query, so it's much easy to understand. One of the popular useful SQL formatting websites that LDD(Little Dumb Doctor) highly
recommended is: www.poorsql.com
. Also many
are using this one: http://www.dpriver.com/pp/sqlformat.htm
Continue to next:
NameLen option: NO truncation in the model output
SAS tutorial home
SAS Cool Tips & Tricks of Proc SQL; How to Rank a variable ?
Statistics tutorial home