How to specify formats while pulling data from Bigquery?

[psrajput]

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;

[ChrisNZ]

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

[Sajid01]

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. 

[ChrisNZ]

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

[psrajput]

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