DATA_MIGRATION/JSON/JSONtoSQL/JSONtoSQL_SAS.sas at master · ParfaitG/DATA_MIGRATION · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
%let fpath = C:\Path\To\Work\Directory;
filename jsonfile 'C:\Path\To\Working\Directory\CLData.json';

** READ IN JSON CONTENT;
data cldata;
    infile jsonfile lrecl=32000 truncover dsd firstobs=3 ;

    input @'"user":' User
        / @'"category":' Category $100.
        / @'"city":' City $100.
        / @'"post":' Post $100.
        / @'"time":' Time $100.
        / @'"link":' Link $255.
        /
        /
    ;
    Category = tranwrd(tranwrd(Category, '"', ''), ',','');
    City = tranwrd(tranwrd(City, '"', ''), ',','');
    Post = tranwrd(tranwrd(Post, '"', ''), ',','');
    Time = tranwrd(tranwrd(Time, '"', ''), ',','');
    Link = tranwrd(tranwrd(Link, '"', ''), ',','');

    _infile_ = tranwrd(_infile_,'"','');
run;

** ASSIGN LIBRARY TO ODBC DATABASE;
libname dbdata odbc complete="DRIVER=SQLite3 ODBC Driver;Database=&fpath\CLData.db;";

** APPEND TO DATABASE TABLE;
proc datasets noprint;
    append base = dbdata.cldata
    data = Work.cldata
    force;
quit;

** UN-ASSIGN ODBC LIBRARY;
libname mydata clear;

put 'Successfully migrated JSON data to SQL database';