Hey Together,
i am working first time with the Option to READ JSON with the specific Libname engine.
The data is stock markets time series, provided by Alphavantage. A sample of daily time series data with 100 records is also available here. The structure is following:
{ "Meta Data": { "1. Information": "Daily Prices (open, high, low, close) and Volumes", "2. Symbol": "MSFT", "3. Last Refreshed": "2018-01-29 14:36:51", "4. Output Size": "Compact", "5. Time Zone": "US/Eastern" }, "Time Series (Daily)": { "2018-01-29": { "1. open": "95.1400", "2. high": "95.4500", "3. low": "93.7600", "4. close": "94.0850", "5. volume": "19659007" }, "2018-01-26": { "1. open": "93.1200", "2. high": "94.0600", "3. low": "92.5800", "4. close": "94.0600", "5. volume": "26348999" }, "2018-01-25": { "1. open": "92.4650", "2. high": "93.2400", "3. low": "91.9300", "4. close": "92.3300", "5. volume": "23983094" } } }
I've tried the code from Chris Hemedingers Post 'Reading data with the SAS JSON libname engine'. But the simple way doesn't shows success... It creates a data set for every day of the time series, looks not very elegant.
Next i tried to create a own JSON map, that's the point where i am struggling now.
Here comes my try:
{ "DATASETS": [ { "DSNAME": "data", "TABLEPATH": "/root/Time Series (Daily)", "VARIABLES": [ { "NAME": "date", "TYPE": "CHARACTER", "PATH": "/root/Time Series (Daily)/date" }, { "NAME": "open", "TYPE": "CHARACTER", "PATH": "/root/Time Series (Daily)/date/1. open", "CURRENT_LENGTH": 7 }, { "NAME": "close", "TYPE": "CHARACTER", "PATH": "/root/Time Series (Daily)/2date/4. close", "CURRENT_LENGTH": 7 }, { "NAME": "volume", "TYPE": "CHARACTER", "PATH": "/root/Time Series (Daily)/date/5. volume", "CURRENT_LENGTH": 8 } ] } ] }
i am using SAS University Edition with the following SAS Code (think you have to change the path to the file with the Map):
/* Assign filename 'resp' for the response */
filename resp temp;
/* Query Alphavantage */
proc http
url="https://www.alphavantage.co/query?function=TIME_SERIES_DAILY%str(&)symbol=MSFT%str(&)apikey=demo"
method= "GET"
out=resp;
run;
/* Print a sample of JSON structure to log */
data _NULL_;
infile resp;
input;
put _INFILE_;
if _N_ gt 18 then stop;
run;
/* Assigning JSON Libname engine */
libname alpha json fileref=resp map='/folders/myshortcuts/import/json_Alpha_DAILY.map';
The expanded dataset 'all' is created successfully but with no records in it....
I don't see what's initially wrong, maybe the JSON structure is unconventional???
Does someone have a trick or suggestion???
Many thanks in advance!!!
Andreas
It's very easy to combine data sets in SAS so maybe that's an option instead?
@atw78de wrote:
It creates a data set for every day of the time series, looks not very elegant.
@Reeza has a good point -- I often use SQL or DATA step to combine the multiple tables instead of creating a JSON map. Since the table names will change with every series that you create, that might be the best option.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.