I'm trying to create a macro that will take a SAS dataset as an input and upload it to Google Sheets. So far I am able to determine the amount of columns and rows needed, and convert the dataset into a JSONL file. This example code has been tested and works: proc http
url="https://sheets.googleapis.com/v4/spreadsheets/&_spreadsheetID./values/Sheet1!A1:&_col.&_row.?valueInputOption=USER_ENTERED"
method="PUT" out=gs_resp
in="%bquote({
"range": "Sheet1!A1:%superq(_col)%superq(_row)",
"majorDimension": "ROWS",
"values": [
["Item","Price","Stocked","Ship Date"],
["Wheel","$20.50","4","3/1/2016"],
["Door","$15","5","3/15/2016"],
["Engine","$100","3","3/20/2012"],
["Hood","$37.5","1","1/1/1900"]
],
})";
headers
"Authorization"="Bearer &access_token."
"client-id:"="&client_id.";
run; Now I just need to replace the hard-coded values list with my JSONL file the macro generates. I've tried using %include but it won't execute (I'm assuming because I am trying to use it inside of a statement?) : proc http
url="https://sheets.googleapis.com/v4/spreadsheets/&_spreadsheetID./values/Sheet1!A1:&_col.&_row.?valueInputOption=USER_ENTERED"
method="PUT" out=gs_resp
in="%bquote({
"range": "Sheet1!A1:%superq(_col)%superq(_row)",
"majorDimension": "ROWS",
"values": [
%include '/some_path_to_a_json_file';
],
})";
headers
"Authorization"="Bearer &access_token."
"client-id:"="&client_id.";
run; These are the contents of the JSON file I'm trying to call. ["Item","Price","Stocked","Ship Date"],["Wheel","$20.50","4","3/1/2016"],["Door","$15","5","3/15/2016"],["Engine","$100","3","3/20/2012"],["Hood","$37.5","1","1/1/1900"] Is there a way around this, or a better way to do this? I've already tried storing the values list inside of a macro variable and then calling the macro variable, and this works, but only for small datasets. I wanted to be able to handle datasets with thousands of records, thus my attempt to convert the dataset into a JSONL file instead. Also, if there is already a better way to upload a SAS dataset to Google Sheets please point me in that direction and I will be eternally grateful 🙂
... View more