EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.


SAS Tutorial in Netezza Access
How to append/update/(create temporary ) datasets in Netezza?

Question 1: How do we append/update a table in Netezza server?
Answer: Say, we want to keep the table(test_data) layout/format in the netezza server, and replace all the records in that data with new dataset(data_in):

        libname dept netezza
        server=nps1 database=test user=myuser pwd=mypwd connection=global;

        proc sql;
        connect to netezza
        (server=nps1 database=test user=myuser pwd=mypwd connection=global);
        execute ( truncate table test_data ) by netezza;
        execute ( insert into test_data
        (var1, var2, var3 )
        select newvar1, newvar2, newvar3
        from data_already_in_netezza
                ) by netezza; quit;
        /******************************************************/
        /* or simply using the following: */
        execute ( insert into test_data
        *
        select a.*
        from data_already_in_netezza as a
                ) by netezza; quit;
        /******************************************************/



Question 2: If you have some dataset locacated outside of Netezza server, and you want use it to do some query with another table inside Netezza server, how can you do that in Netezza server(not in SAS)?

Answer: We can create a temporary dataset in Netezza server, and fill the
temporary table with the records from the data out of Netezza Server. Note that to make full use of temporary tables, the CONNECTION=GLOBAL connection option is necessary.
        libname saslib base ’SAS-Data-Library’;

        libname dept netezza server=nps1 database=test
        user=myuser pwd=mypwd connection=global;

        libname temp netezza server=nps1 database=test
        user=myuser pwd=mypwd connection=global;

        proc sql;
        connect to netezza (server=nps1 database=test user=myuser pwd=mypwd
        connection=global);
        execute (create temporary table temptab1 (dname char(20), deptno int))
        by netezza; quit;


This example shows how to use a heterogeneous join with a temporary table to perform a homogeneous join on the DBMS, insted of reading the DBMS table into SAS to perform the join. By using the table that was created previously, you can copy SAS data into the temporary table to perform the join.

        proc sql;
        connect to netezza (server=nps1 database=test
        user=myuser pwd=mypwd connection=global);
        insert into temp.temptab1 select * from saslib.joindata;
        select * from dept.deptinfo info, temp.temptab1 tab
        where info.deptno = tab.deptno;

        execute (delete from temptab1) by netezza;
        quit;
        /******************************************************/



Question 3: How do we get the percentile from Netezza SQL?
Answer: We can use two functions PERCENTILE_CONT and PERCENTILE_DISC to get the percentile in SQL. Note that these functions may not return the same value. This is because PERCENTILE_CONT interpolates the appropriate value, whether or not it exists in the data set(usually the average of two values), while PERCENTILE_DISC always returns an actual value from the set.

        SELECT DISTINCT Name,
        Percentile_Cont(0.5) Within Group (Order by Rate) As Median_estimate,
        Percentile_Disc(0.5) Within Group (Order by Rate) AS Median_actual,
        Percentile_Disc(0.1) Within Group (Order by Rate) AS Percentile_10th,
        group by Name;

        SELECT city, sum(amt), ntile(4) over(ORDER BY sum(amt))
        *split into 4 bins;
        FROM sales_tbl GROUP BY city;


Here is the code to get the corresponding percentile:
        proc sql;
        connect to netezza (server=nps1 database=test
        user=myuser pwd=mypwd connection=global);
        execute (create table table1 as select var1,
        percent_rank() over ( order by var1 desc ) as var1_pcnt )
        by netezza; quit;

Related links:

Continue to next: Tips in Netezza: Sort Data in Netezza not working?   SAS tutorial home
Back to: Tips for large datasets-III: How to query two large datasets in Netezza?   Statistics tutorial home