BookmarkSubscribeRSS Feed
psrajput
Obsidian | Level 7

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;

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

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

Sajid01
Meteorite | Level 14

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
Tourmaline | Level 20

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

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 642 views
  • 0 likes
  • 3 in conversation