Reading time series data with LIBNAME JSON

atw78de — Mon, 29 Jan 2018 20:49:10 GMT

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

Re: Reading time series data with LIBNAME JSON

Reeza — Mon, 29 Jan 2018 21:26:10 GMT

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.

Re: Reading time series data with LIBNAME JSON

ChrisHemedinger — Mon, 29 Jan 2018 22:51:32 GMT

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

topic Reading time series data with LIBNAME JSON in SAS Programming

Reading time series data with LIBNAME JSON

Re: Reading time series data with LIBNAME JSON

Re: Reading time series data with LIBNAME JSON