topic Re: Passing Dataset to Google Sheets API (Stuck using %include for .json file) in SAS Programming

Passing Dataset to Google Sheets API (Stuck using %include for .json file)

adamdeluca870 — Wed, 20 Mar 2019 13:03:44 GMT

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 🙂

Re: Passing Dataset to Google Sheets API (Stuck using %include for .json file)

noling — Wed, 20 Mar 2019 15:45:42 GMT

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.

Re: Passing Dataset to Google Sheets API (Stuck using %include for .json file)

adamdeluca870 — Thu, 21 Mar 2019 13:15:36 GMT

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;

Re: Passing Dataset to Google Sheets API (Stuck using %include for .json file)

noling — Thu, 21 Mar 2019 13:20:08 GMT

Thanks for sharing your nice and clean complete code! I'm sure others will be able to reference it!