Extraction and consolidation of fiscal data via REST API

The script defines a macro '%json' that loops through a range of years and result pages. For each iteration, it calls a REST API via PROC HTTP, reads the returned JSON stream via the LIBNAME JSON engine, and consolidates the results into a single table via PROC SQL (UNION). Finally, a Data Step applies Korean labels and filters invalid records. Note: The path 'C:\json\' will need to be adapted for a SAS^© Viya^™ (Linux) environment.

Data Analysis

Type : EXTERNAL

Data comes from the URL 'http://openapi.openfiscaldata.go.kr/RevenuesSettled' (public API).

1 Code Block

SETUP

Explanation :
Definition of macro variables (path, library, API key) and assignment of the output library.

Copied!

1	%let dir=C:\json\;
2	%let lib=json;
3	%let String01=WBQMR1000052520180323030651FWHGU;/apiKey/
4
5	LIBNAME &lib "&dir";

2 Code Block

PROC HTTP Data

Explanation :
Definition of the main macro. It initializes the table with the first request, then loops through years and pages to accumulate data via HTTP GET requests and SQL unions.

Copied!

1	%macro json(data_final, String05, date_s, date_e);
2
3	%let url=&string05?FSCL_YY=&date_s.&key=&string01&type=json&pindex=1&psize=1000;
4
5	filename out temp;PROC HTTP url="&url" method="get" out=out;RUN;
6	LIBNAME raw json fileref=out;
7
8	DATA &lib .&data_final;SET raw.Revenuessettled_row;RUN;
9
10	%DO date_want=&date_s %to &date_e;
11	%DO string02=1 %to 4;
12
13	%let url=&string05?FSCL_YY=&date_want.&key=&string01&type=json&pindex=&string02&psize=1000;
14
15	filename out temp;PROC HTTP url="&url" method="get" out=out;RUN;
16	LIBNAME raw json fileref=out;
17
18	PROC SQL;
19	create TABLE &lib .&data_final as
20	select distinct * from
21	(select a.* from &lib .&data_final as a union select b.* from raw.Revenuessettled_row as b)
22	QUIT;
23	RUN;
24
25	%END;
26	%END;
27	%mend;

3 Code Block

MACRO CALL Data

Explanation :
Macro call to extract data from 2012 to 2015.

Copied!

1	%json(data_final=longdata_002, String05=http://openapi.openfiscaldata.go.kr/RevenuesSettled, date_s=2012, date_e=2015);
2

4 Code Block

DATA STEP Data

Explanation :
Cleaning the final table (removing missing years) and applying metadata (column labels) in Korean.

Copied!

1	DATA &lib .longdata_002;
2	SET &lib .longdata_002;
3	IF FSCL_YY="" THEN delete;
4	label FSCL_YY=회계연도;
5	/* ... (autres labels omis pour brièveté) ... */
6	label NRC_AMT =미수납액(원);
7	RUN;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste