Juletip #13 - Reading data from SSB using REST API and import data to SAS using Excel Ranges.

TorOveKilnesSAS — Tue, 13 Dec 2022 11:02:44 GMT

Introduction:
The code is used for getting data from 2 statistics at SSB using their API and SAS proc HTTP and read the resulting Excel sheet into SAS.
It can be seen as SAS variant of using REST APIs, using proc http, in the same way as Python do in juletip $9

The SAS code is uing proc import and RANGE in order to only read the part of the Excel sheet that is of interest. The text below is not wanted.

NOTE: Due to some error somewhere, the downloaded Excel file should be opened in Excel, then just saved and Exit Excel.
This changes some metadata in the Excel file that makes it possible for SAS to read just parts (Range) of the excel file.

The steps:
0. Set up filenames for proc http
1. To get the excel data in a folder using the SAS proc HTTP and SSB API. The URLs are gotten from SSB links above in section "API spørring.."
2. Read the Excel sheets into SAS, and keep the row with data

How to run:
Due to the "metadata issue", the steps for running this is:
1. Open the program and change the location where the resulting Excel files should be saved.
This is the macro variable "excel_root" at the top of the program.
2. Run step 0 and Step 1 and make sure that 2 Excel files are produced in your location.
This ends at the line:
** STEP 2: Read the Excel sheets into SAS, and keep the row with data

In the SAS log, check that the 2 proc http procedures ends with return code: NOTE: 200 OK

The files are:
SSB_KPI.xlsx
SSB_KPI_Varer_Tjenester.xlsx

3. Run the remaining part of the program. This creates the WORK dataset SSB_KPI and SSB_KPI_Varer_Tjenester.

Things to try out:
- Change output in the request file from "xlsx" (excel output) to "json-stat2". For SAS proc http, look at the SAS Forum presentation "Rest just got Easy..".
This creates JSON output. NOTE: Now it may be useful to look at the longer SSB documentation where the short video is and Github examples.
NOTE that now it us useful not to download the resulting file, but let them stay in TEMP filename in SAS.
Adapt the Excel part to read JSON instead of Excel.
Adapt the columns exported in the request file
- Find your own API interfaces using other SSB table and adapt the program.
- Find other sources of REST API interfaces where the same logic can be applied.

The codefile "Juletip_13des2022_Read_Excel_via_SSB_API.sas" is attached and also contain some documentation.

topic Juletip #13 - Reading data from SSB using REST API and import data to SAS using Excel Ranges. in SAS Community Nordic

Juletip #13 - Reading data from SSB using REST API and import data to SAS using Excel Ranges.