examples of sas proc sql connect to db with execute

*** sas proc sql connect to db with execute;

 

* in sas to run proc sql contains a query passedthrough sas to sas sql processor;

*SAS;

proc sql;

select employee_title as title, avg(employee_years),

   freq(employee_id)

      from sql.employee

      group by title

      order by title;

* Query passed through;

select * from connection to remote

   (select employee_title as title,

    avg(employee_years),

    freq(employee_id)

       from sql.employee

       group by title

       order by title);

 

* drop table, create table and insert an obs into table;

proc sql;

    execute(drop table ' My Invoice ') by db;

    execute(create table ' My Invoice '(

       ' Invoice Number ' LONG not null,

       ' Billed To '  VARCHAR(20),

       ' Amount '  CURRENCY,

       ' BILLED ON '  DATETIME)) by db;

    execute(insert into  ' My Invoice '

       values( 12345, 'John Doe', 123.45, #11/22/2003#)) by db;

quit;

 

* insert into table base from another table named one;

proc sql;

insert into base ( source, a, b, d   )

select            source, a, b, ' '

  from one;

quit;

 

* join a db table facebook_ads with another table sasprod.fb_ad_fromsas by ad_id;

proc sql;

   connect to oracle (user=sas password=sas path=db1);

   execute (update db1.facebook_ads m set

            (m.rank, m.ad_work_item_id)=

            (select a.rank, a.ad_work_item_id

             from sasprod.fb_ad_fromsas a

             where m.ad_wid=a.ad_id)

            where exists (select 1 from sasprod.fb_ad_fromsas a where m.ad_id=a.ad_id)

           )

   by oracle;

   disconnect from oracle;

quit;

 

* update mysql db;

* update the conversion_rate and updated_at in table projected_conversion_rates on mysql db named reporting_production by the values in sasdb.projected_merchant_conv_sas on equalling of payer_id, event_id and cutoff values;

proc sql;

        connect to mysql (user=xxx password=xxx database=reporting_production server="server_m1" port=8888);

        execute

        (update projected_conversion_rates pcr inner join sasdb.projected_merchant_conv_sas a on (pcr.payer_id=a.payer_id and pcr.event_id=a.event_id and pcr.cutoff_min=a.cutoff_min and pcr.cutoff_max=a.cutoff_max)

        set pcr.conversion_rate=a.conversion_rate, pcr.updated_at=a.updated_at)

        by mysql;

        disconnect from mysql;

quit;

 

 

 

libname mylib 'c:\sales';

 

* another example to join db table with client table;

proc sql;

   connect to remote

      (server=tso.shr1 dbms=db2

       dbmsarg=(ssid=db2p));

   select * from mylib.sales08,

      connection to remote

         (select qtr, division,

                 sales, pct

            from revenue.all08

            where region='Southeast')

      where sales08.div=division;

 

 

*** docs: http://support.sas.com/documentation/cdl/en/connref/61908/HTML/default/viewer.htm#srspt.htm;