topic Re: How to specify formats while pulling data from Bigquery? in SAS Programming

How to specify formats while pulling data from Bigquery?

psrajput — Thu, 03 Jun 2021 10:34:11 GMT

Hi,

When we pull data from BQ in SAS, it somehow converts the format of some of the columns, because I think it uses bq resources to run the query. Which is affecting the data values.

For example, one of the values in a column is 14277.4, whereas after pulling this data in SAS, it comes as 14277. I expect this is because of the format change. Actual format should be simple numeric but it is coming as dollar format. Is there a way to correct this issue?

proc sql;

connect to bigquery (project="project_name" schema="schema_name" cred_path="cred_location" bulkunload=yes bl_bucket="bucket_name" max_char_len=80);

create table lib.table as select * from connection to bigquery (

select * from schema.table;

);

disconnect from bigquery;

quit;

Although, the below runs fine, as it it running on SAS itself.

libname bq bigquery project="project_name" schema="schema_name" cred_path="cred_location" bulkunload=yes bl_bucket="bucket_name" max_char_len=80;

data lib.table;

set src.table;

run;

Re: How to specify formats while pulling data from Bigquery?

ChrisNZ — Thu, 03 Jun 2021 10:20:00 GMT

This looks like a technical support issue. Have you contacted them?

Re: How to specify formats while pulling data from Bigquery?

Sajid01 — Thu, 03 Jun 2021 13:37:07 GMT

Hello @psrajput
When you are using SQL pass through, the query is running in the DBMS (here BQ), so data types, formats etc. of the DBMS apply and you may possibly need to handle conversion too.
Whereas when using implicit SQL / libname statement, the processing is taking in SAS and its data type, formats, precision etc. will apply..
That is why you are seeing the difference.

Re: How to specify formats while pulling data from Bigquery?

ChrisNZ — Sat, 05 Jun 2021 06:30:57 GMT

@Sajid01 SAS should be handling the data types. There is no valid reason for the behaviour reported here.

Re: How to specify formats while pulling data from Bigquery?

psrajput — Wed, 09 Jun 2021 14:39:16 GMT

I agree. But was just checking if there is a way. I think will have to continue with SAS data or normal SQL.