topic Re: Proc SQL into Google Big Query in SAS Programming

Proc SQL into Google Big Query

Kaushansky — Tue, 12 Sep 2023 19:44:43 GMT

How should set up SAS desktop to access GBQ? I am able to access it via Python pass-through using a project ID. How do I set up access via SAS? Thank you.

Re: Proc SQL into Google Big Query

SASKiwi — Tue, 12 Sep 2023 21:31:09 GMT

You need a SAS/ACCESS product installed and licensed, like SAS/ACCESS to Google Big Query or SAS/ACCESS to ODBC. Run this code to confirm what products you have available:

proc product_status;
run;

proc setinit;
run;

Re: Proc SQL into Google Big Query

Kaushansky — Wed, 13 Sep 2023 14:19:44 GMT

Yes, here is what I have:

Original site validation data

Current version: 9.04.01M7P080520

Site name: 'NEW YORK UNIVERSITY - T&R - SFA'.

Site number: 70084653.

CPU A: Model name='' model number='' serial=''.

Expiration: 30JUN2024.

Grace Period: 45 days (ending 14AUG2024).

Warning Period: 48 days (ending 01OCT2024).

System birthday: 06JUN2023.

Operating System: WX64_WKS.

Product expiration dates:

---Base SAS Software

30JUN2024 (CPU A)

---SAS/STAT

30JUN2024 (CPU A)

---SAS/GRAPH

30JUN2024 (CPU A)

---SAS/ETS

30JUN2024 (CPU A)

---SAS/FSP

30JUN2024 (CPU A)

---SAS/OR

30JUN2024 (CPU A)

---SAS/AF

30JUN2024 (CPU A)

---SAS/IML

30JUN2024 (CPU A)

---SAS/QC

30JUN2024 (CPU A)

---SAS/SHARE

30JUN2024 (CPU A)

---SAS/ASSIST

30JUN2024 (CPU A)

---SAS/CONNECT

30JUN2024 (CPU A)

---SAS/EIS

30JUN2024 (CPU A)

---SAS/GIS

30JUN2024 (CPU A)

---SAS/SHARE*NET

30JUN2024 (CPU A)

---MDDB Server common products

30JUN2024 (CPU A)

---SAS Integration Technologies

30JUN2024 (CPU A)

---SAS/Secure 168-bit

30JUN2024 (CPU A)

---SAS/Secure Windows

30JUN2024 (CPU A)

---SAS Enterprise Guide

30JUN2024 (CPU A)

---SAS Bridge for ESRI

30JUN2024 (CPU A)

---OR OPT

30JUN2024 (CPU A)

---OR PRS

30JUN2024 (CPU A)

---OR IVS

30JUN2024 (CPU A)

---OR LSO

30JUN2024 (CPU A)

---SAS/ACCESS Interface to DB2

30JUN2024 (CPU A)

---SAS/ACCESS Interface to Oracle

30JUN2024 (CPU A)

---SAS/ACCESS Interface to SAP ASE

30JUN2024 (CPU A)

---SAS/ACCESS Interface to PC Files

30JUN2024 (CPU A)

---SAS/ACCESS Interface to ODBC

30JUN2024 (CPU A)

---SAS/ACCESS Interface to OLE DB

30JUN2024 (CPU A)

---SAS/ACCESS Interface to R/3

30JUN2024 (CPU A)

---SAS/ACCESS Interface to Teradata

30JUN2024 (CPU A)

---SAS/ACCESS Interface to Microsoft SQL Server

30JUN2024 (CPU A)

---SAS/ACCESS Interface to MySQL

30JUN2024 (CPU A)

---SAS/IML Studio

30JUN2024 (CPU A)

---SAS Workspace Server for Local Access

30JUN2024 (CPU A)

---SAS/ACCESS Interface to Netezza

30JUN2024 (CPU A)

---SAS/ACCESS Interface to Aster nCluster

30JUN2024 (CPU A)

---SAS/ACCESS Interface to Greenplum

30JUN2024 (CPU A)

---SAS/ACCESS Interface to SAP IQ

30JUN2024 (CPU A)

---SAS/ACCESS to Hadoop

30JUN2024 (CPU A)

---SAS/ACCESS to Vertica

30JUN2024 (CPU A)

---SAS/ACCESS to Postgres

30JUN2024 (CPU A)

---SAS/ACCESS to Impala

30JUN2024 (CPU A)

---SAS/ACCESS to Salesforce

30JUN2024 (CPU A)

---SAS/ACCESS to HAWQ

30JUN2024 (CPU A)

---SAS/ACCESS to Amazon Redshift

30JUN2024 (CPU A)

---High Performance Suite

30JUN2024 (CPU A)

---SAS/ACCESS to SAP HANA

30JUN2024 (CPU A)

---SAS/ACCESS Interface to the PI System

30JUN2024 (CPU A)

---SAS/ACCESS to JDBC

30JUN2024 (CPU A)

---SAS/ACCESS to Snowflake

30JUN2024 (CPU A)

Re: Proc SQL into Google Big Query

SASKiwi — Wed, 13 Sep 2023 21:53:02 GMT

I don't see Big Query listed but SAS/ACCESS Interface to ODBC should work. You will need to obtain an ODBC driver for Big Query and install it on your PC. Do you have a help desk who can advise on this?

Re: Proc SQL into Google Big Query

Kaushansky — Wed, 13 Sep 2023 23:13:36 GMT

I see. How do I get access the BoG Query ODBC driver ?

Re: Proc SQL into Google Big Query

SASKiwi — Thu, 14 Sep 2023 02:51:06 GMT

Just Google it. I did and found this: https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers

What bitness is your SAS? If 64-bit then download the 64-bit ODBC driver for Windows and install it. If 32-bit use the 32-bit ODBC driver for Windows.

Once installed it should show in the ODBC Administrator tool:

Re: Proc SQL into Google Big Query

Kaushansky — Thu, 14 Sep 2023 11:20:23 GMT

Great. Once that is done. How should I set up SAS/ACCESS to GCP?

Re: Proc SQL into Google Big Query

SASKiwi — Fri, 15 Sep 2023 00:58:34 GMT

Probably the easiest way to try this out is to create a GBQ DSN in the ODBC Administrator similar to this:

There's a Test Connection button in the tool to check your connection details. Try that and if it works, the next step is to try connecting in SAS. Let's say you created a DSN (Data Source Name) called GBQ. Now try this in SAS:

libname MyGBQ ODBC DSN = GBQ;

Check your SAS log to see if it connected OK or not. Note you will have to save your user name and password in the DSN for this to work. Alternatively you will need to add those to your LIBNAME.

Re: Proc SQL into Google Big Query

Kaushansky — Fri, 15 Sep 2023 19:24:23 GMT

Looks like I have it - right?

So how should I test it in SAS?

Re: Proc SQL into Google Big Query

SASKiwi — Fri, 15 Sep 2023 23:30:17 GMT

Try a LIBNAME as already mentioned, with the DSN you have created.

libname MyGBQ ODBC DSN = 'Google BigQuery';

Check the SAS log to see if it works or not. There are most likely other options that need to be added to the LIBNAME, but one step at a time.

Re: Proc SQL into Google Big Query

Kaushansky — Sat, 16 Sep 2023 16:14:26 GMT

Here is the error I get:

Re: Proc SQL into Google Big Query

Kurt_Bremser — Sat, 16 Sep 2023 19:09:22 GMT

Looks like some configuration for the Simba ODBC driver is missing. Have a look at the documentation: https://docs.posit.co/drivers/1.6/pdf/Simba ODBC Driver for Google BigQuery Install and Configuration Guide.pdf

Re: Proc SQL into Google Big Query

SASKiwi — Sun, 17 Sep 2023 20:17:50 GMT

Did you add any login details to your DSN like this?