SAS code to read JSON column data from Teradata table

Question

I have a requirement to read data from Teradata table T1 in SAS.

When i run the below query from SAS, it will fetch the data but one of the column is missing which is of JSON data type.

Select * from T1;

Hence i tried with below query: Select CAST(json_column as char(5000)) from T1; When I run this query in Teradata sql assistant, it will work but throws error when I use it in sas EG. CAST & CHAR sql functions won't work in SAS? I appreciate any help here.

Thanks in advance

Show your full code. If you're accessing it via pass through that would be valid, if you're using SAS PROC SQL via implicit pass through that would not work. You could try the SAS equivalent PUT. PUT(json_column, $500.) as column1 — Reeza

Tom Tom · Accepted Answer · 2021-05-12T19:45:23

You need to use Teradata code in Teradata, not in SAS. So use explicit pass thru.

proc sql;
  connect to teradata .... ;
  select * from connection to teradata
    (select CAST(json_column as char(5000)) as JSONTEXT from T1)
  ;
quit;

You might also want to use Teradata code to parse out the actual values you want from the JSON text object instead of just returning a string.

https://www.google.com/search?q=%40teradata.com+extracting+from+json+text

SAS code to read JSON column data from Teradata table

1 Answers