Reading time series data with LIBNAME JSON

[atw78de]

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

[Reeza]

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.

---

[ChrisHemedinger]

@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.