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
... View more