Creating Json string to send data to API

[Faiz_Qureshi]

I want to send data in a json format to API to fetch data.

but when i'm using the below code, i'm getting error like this in the log

I think the data is not converted as Json String like for this record .

I have a data set "Input_data" with columns like "vCustomerFirstName, vCustomerLastName, vCustomerMiddleName, nCustomerGender, vCustomerPhoneNumber, vCustomerDateOfBirth, vCustomerIDProofDocumentName and vCustomerIDProofDocumentNumber".

Help required - method to send dataset in a json string format to API and saving the output.

/* Code */

/* Create an empty dataset to hold the output */
data output_data;
length response $32767;
stop;
run;

/* Read the JSON string from the input dataset */

data ;
set inputs_2;
length json_string $3200;
json_string = cats('{ "vCustomerFirstName": "', vCustomerFirstName, '", "vCustomerLastName": "', vCustomerLastName, '", "vCustomerMiddleName": "', vCustomerMiddleName, '", "nCustomerGender": "', nCustomerGender, '", "vCustomerPhoneNumber": "', vCustomerPhoneNumber, '", "vCustomerDateOfBirth": "', vCustomerDateOfBirth, '", "vCustomerIDProofDocumentName": "', vCustomerIDProofDocumentName, '", "vCustomerIDProofDocumentNumber": "', vCustomerIDProofDocumentNumber, '"}');
json_string = compress(json_string, ' ');
run;

/* Put the JSON string in an external file */

filename json_in temp;
data _null_;
file json_in;
put json_string;
run;

/* Post the JSON query to the API and get the response */

filename resp temp;
proc http
method="POST"
url=url
ct="application/json"
in=json_in
out=resp;
run;

/* Read the response from the API and save it in the output dataset */
data _null_;
infile resp;
input;
response = _infile_;
run;

 /* End of the code */

[ChrisHemedinger]

I recommend looking at PROC JSON to see if it can create the simple JSON you need.  Good reference in this paper.

Also, you can "validate" the JSON you create by using the JSONPP function (JSON pretty print). See more in Using JSON Data With SAS .

[Tom]

Why make an empty dataset that you never use?

Why go to the trouble to put the JSON string into a character variable if you actually need it in a text file?

Why read in the response text if you never write it out to a dataset?

Assuming the JSON is just simple list like 

[ "name1":"string1" , "name2":"string2", "name3":number,"name4":number ]

And the names of the variables in your input dataset match the tags you want to use in the generated JSON then you could just use something like this to make the JSON file, post it and read the response as a series of lines of text.

filename json_in temp;
filename resp temp;

* Write the JSON to the file ;
data _null_;
  length _name_ $32 i 8;
  set HAVE;
  file json_in ;
  array _c _character_;
  array _n _numeric_;
  sep='[' ;
  do i=2 to dim(_c);
     _name_=vname( _c[i] );
     put sep _name_ :$quote. ':' _c[i] :$quote. ;
     sep = ',' ;
  end;
  do i=2 to dim(_n);
     _name_=vname( _n[i] );
     put sep _name_ :$quote. ':' _n[i] ;
  end;
  put ']' ;
run;

* Post the JSON query to the API and get the response ;
proc http
  method="POST"
  url=url
  ct="application/json"
  in=json_in
  out=resp
;
run;

* Read the response ;
data WANT;
  infile resp truncover ;
  input response $200.;
run;

[Faiz_Qureshi]

Thank for you help. Actually I'm new to SAS studio and working on someone else's code.

What i want to do is-

There is dataset "Input_data", which have variables mentioned above.

I want to send the records in this data as json string to API and store the output.

can you please provide a code to read records from the data  "input_data" and send it to API as a json-string.

[Tom]

You need to know 

1) What JSON text you need to generate for that API.  What are the names of the fields? What types of values should they contain?

2) What data you have in your SAS dataset.  What are the names of the variables? Are they numeric or character? What types of values do they have?

3) How they relate to each other.  For example you might have a variable in the SAS dataset called NAME but you need to generate the value for a JSON tag named Person_Name.

Also does the dataset have multiple observations?  Can the API handle data for multiple "things" at once?  Or do you need to make many separate API calls?

Also how can you tell whether the API call worked? What type of response file does the API return?  How can you tell if the response means it worked?

[Faiz_Qureshi]

To answer your queries about data info.

I have attached the dataset contents

2. Also does the dataset have multiple observations?  Can the API handle data for multiple "things" at once?  Or do you need to make many separate API calls?

I'm not sure about this, but i want to send one record at a time and store the output for that record like a do loop.

3. Also how can you tell whether the API call worked? What type of response file does the API return?  How can you tell if the response means it worked?

The API call worked.

I got the output when i send a single record. but i entered the record manually with below code.

Thank you for your time and efforts.