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 🙂
Can you write the entire contents of your IN options to an external file (including your data rows), then read in that file as a file ref for your IN option? I'm looking at this example.
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Can you write the entire contents of your IN options to an external file (including your data rows), then read in that file as a file ref for your IN option? I'm looking at this example.
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Worked like a charm.
For completeness, proc json can be used to easily convert the dataset into the correct format
proc json out=json pretty nosastags nokeys;
export &_libname..&_memname.;
run;
Then the entire payload can be written to a text file:
data _null_;
file payload;
infile json firstobs=2 end=eof;
input;
if _N_ = 1 then
do;
put '{';
put '"range": "Sheet1!A1:'@;
put "&_col.&_row."@;
put '",';
put '"majorDimension": "ROWS",';
put '"values":[';
put "%superq(_headers),";
end;
if eof then
do;
put _infile_ @;
put ',';
put '}';
end;
else
do;
put _infile_;
end;
run;
Thanks for sharing your nice and clean complete code! I'm sure others will be able to reference it!
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.