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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 683 views
  • 0 likes
  • 3 in conversation