Accepted Solutions
@Tal,
The below code will parse the JSON that you included in your original post. This code has the advantage that one does not need to know the variable names beforehand. This is somewhat unsophisticated code in that I'm not checking for embedded commas in either the variable names or the values. If there are embedded commas, this code would have to be modified.
FILENAME In_Data 'C:\Users\jbarbou3\Documents\SAS\Pgm\Training\JSON_Parse\JSON_Data.txt';
DATA WORK.Parsed_Data;
DROP _:;
LENGTH _JSON_Data $32767;
LENGTH _Txt_Wrk1 $1026;
LENGTH Var $32;
LENGTH Value $1026;
INFILE In_Data LENGTH = _JSON_Length
RECFM = V
;
INPUT @1 _JSON_Data $VARYING32767. _JSON_Length;
** The first character is a {, so start in position 2. **;
_Position = 2;
SUBSTR(_JSON_Data, _JSON_Length, 1) = ',';
DO WHILE (_Position < _JSON_Length);
_Segment_Length = INDEXC(SUBSTR(_JSON_Data, _Position), ',');
_Txt_Wrk1 = SUBSTR(_JSON_Data, _Position, (_Segment_Length - 1));
Var = COMPRESS(SUBSTR(_Txt_Wrk1, 1, (INDEXC(_Txt_Wrk1, ':') - 1)), '"');
Value = COMPRESS(SUBSTR(_Txt_Wrk1, (INDEXC(_Txt_Wrk1, ':') + 1)), '"');
_Position = _Position + _Segment_Length;
OUTPUT;
END;
RUN;The above code yields the following results:
Jim
Well, I was thinking about this just a bit more. If one is content with collecting a series of variable names and their values by parsing the JSON data, then my previous reply is sufficient.
However, if one were to desire to have the variable names become separate variable names in a SAS data set, that's a bit more involved. There are no doubt other ways to do this, but since I rather like macros, I coded the following:
FILENAME In_Data 'C:\Users\jbarbou3\Documents\SAS\Pgm\Training\JSON_Parse\JSON_Data.txt';
**------------------------------------------------------------------------------**;
DATA WORK.Parsed_Data;
DROP _:;
LENGTH _JSON_Data $32767;
LENGTH _Text_Work $1026;
LENGTH _Var_Name $32;
LENGTH Value $1026;
INFILE In_Data LENGTH = _JSON_Length
RECFM = V
;
INPUT @1 _JSON_Data $VARYING32767. _JSON_Length;
** The first character is a {, so start in position 2. **;
_Position = 2;
_Iteration = 0;
SUBSTR(_JSON_Data, _JSON_Length, 1) = ',';
DO WHILE (_Position < _JSON_Length);
_Iteration + 1;
_Segment_Length = INDEXC(SUBSTR(_JSON_Data, _Position), ',');
_Text_Work = SUBSTR(_JSON_Data, _Position, (_Segment_Length - 1));
_Var_Name = COMPRESS(SUBSTR(_Text_Work, 1, (INDEXC(_Text_Work, ':') - 1)), '"');
Value = COMPRESS(SUBSTR(_Text_Work, (INDEXC(_Text_Work, ':') + 1)), '"');
_Position = _Position + _Segment_Length;
IF _N_ = 1 THEN
CALL SYMPUTX(CATS('Var_Name', PUT(_Iteration, F3.)), _Var_Name);
OUTPUT;
END;
IF _N_ = 1 THEN
CALL SYMPUTX('Var_Count', PUT(_Iteration, F3.));
RUN;
**------------------------------------------------------------------------------**;
%MACRO List_Vars(Comma=NO);
%IF %QUPCASE(&Comma) = YES %THEN
%DO;
%LET Comma = ,;
%END;
%ELSE
%DO;
%LET Comma = ;
%END;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
%IF &i = &Var_Count %THEN
%LET Comma = ;
&&Var_Name&i &Comma
%END;
%MEND List_Vars;
**------------------------------------------------------------------------------**;
%MACRO Equate_Vars;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
IF _Iteration = &i THEN
&&Var_Name&i = Value;
%END;
%MEND Equate_Vars;
**------------------------------------------------------------------------------**;
DATA WORK.Final_Data;
DROP Value;
DROP _:;
RETAIN _Iteration;
RETAIN %List_Vars;
SET WORK.PARSED_DATA;
_Iteration + 1;
IF _Iteration > &Var_Count THEN
_Iteration = 1;
%Equate_Vars;
IF _Iteration = &Var_Count THEN
DO;
OUTPUT;
CALL MISSING(%List_Vars(Comma=YES));
END;
RUN;
**------------------------------------------------------------------------------**;
The result of which is a SAS data set with column names parsed out from the JSON data:
This of course assumes that every iteration of the JSON data has the same set of variables. If all the variable names are not the same, then I think the JSON files would have to be parsed into separate SAS data sets which doesn't sound very practical.
Jim
@Tal,
It was an interesting exercise, so I went ahead and wrote code that can create SAS variables based on JSON formatted data even if not all of the same variables are in each instance of JSON formatted data. If you ever need it, here it is. 🙂
As test data, I added a few more rows. Some of the additional rows have columns that previous rows do not. The added columns are:
- new
- vip
- special
- promo
Here are the results. Note that for rows in which the JSON data did not contain a value for a given column that the value of that column is "missing".
Here's the code. I hope it comes in handy some time:
%Time_Stamp(START);
**------------------------------------------------------------------------------**;
OPTIONS VALIDVARNAME = V7;
%LET Cmnt = ;
%LET Width = 75;
**------------------------------------------------------------------------------**;
FILENAME In_Data 'C:\Users\jbarbou3\Documents\SAS\Pgm\Training\JSON_Parse\JSON_Data.txt';
**------------------------------------------------------------------------------**;
DATA WORK.Parsed_Data;
DROP _:;
LENGTH _JSON_Data $32767;
LENGTH _Segment $1026;
LENGTH _Var_Name $32;
LENGTH Row_Nbr 4;
LENGTH Var_Nbr 4;
LENGTH Value $1026;
RETAIN _Max_Nbr 0;
IF _N_ = 1 THEN
DO;
CALL MISSING(Var_Nbr, _Var_Name);
DECLARE HASH Vars(ORDERED: 'A');
Vars.DEFINEKEY('_Var_Name');
Vars.DEFINEDATA('Var_Nbr');
Vars.DEFINEDONE();
END;
IF _End_of_Data THEN
DO;
CALL SYMPUTX('Var_Count', PUT(_Max_Nbr, F3.), 'G');
END;
INFILE In_Data LENGTH = _JSON_Length
RECFM = V
END = _End_of_Data
;
INPUT @1 _JSON_Data $VARYING32767. _JSON_Length;
** The first character is a {, so start in position 2. **;
_Position = 2;
SUBSTR(_JSON_Data, _JSON_Length, 1) = ',';
DO WHILE (_Position < _JSON_Length);
_Segment_Length = INDEXC(SUBSTR(_JSON_Data, _Position), ',');
_Segment = SUBSTR(_JSON_Data, _Position, (_Segment_Length - 1));
_Var_Name = COMPRESS(SUBSTR(_Segment, 1, (INDEXC(_Segment, ':') - 1)), '"');
Value = COMPRESS(SUBSTR(_Segment, (INDEXC(_Segment, ':') + 1)), '"');
_Position = _Position + _Segment_Length;
_RC = Vars.FIND();
IF _RC = 0 THEN
DO;
CALL SYMPUTX(CATS('Var_Name', PUT(Var_Nbr, F3.)), _Var_Name, 'G');
IF Var_Nbr > _Max_Nbr THEN
_Max_Nbr = Var_Nbr;
END;
ELSE
DO;
_Max_Nbr + 1;
CALL SYMPUTX(CATS('Var_Name', PUT(_Max_Nbr, F3.)), _Var_Name, 'G');
Var_Nbr = _Max_Nbr;
_RC = Vars.ADD();
IF _RC > 0 THEN
DO;
PUTLOG "&Err2 ";
PUTLOG "&Err2 %Format_Dashes(&Width)";
PUTLOG "&Err1 | Unable to add variable to Hash table. " _RC=;
PUTLOG "&Err2 %Format_Dashes(&Width)";
END;
END;
Row_Nbr = _N_;
OUTPUT;
END;
RUN;
**------------------------------------------------------------------------------**;
&Cmnt.PROC PRINT DATA=WORK.PARSED_DATA;
&Cmnt.RUN;
**------------------------------------------------------------------------------**;
%MACRO List_Vars(Comma=NO);
%IF %QUPCASE(&Comma) = YES %THEN
%DO;
%LET Comma = ,;
%END;
%ELSE
%DO;
%LET Comma = ;
%END;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
%IF &i = &Var_Count %THEN
%LET Comma = ;
&&Var_Name&i &Comma
%END;
%MEND List_Vars;
&Cmnt%PUT &Nte1 &=Var_Count %List_Vars(Comma=YES);
**------------------------------------------------------------------------------**;
%MACRO Equate_Vars;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
IF Var_Nbr = &i THEN
&&Var_Name&i = Value;
%END;
%MEND Equate_Vars;
&Cmnt%PUT &Nte1 %QUOTE(%Equate_Vars);
**------------------------------------------------------------------------------**;
DATA WORK.Final_Data;
DROP Row_Nbr;
DROP Var_Nbr;
DROP Value;
DROP _:;
RETAIN %List_Vars;
SET WORK.PARSED_DATA;
BY Row_Nbr;
%Equate_Vars;
IF LAST.Row_Nbr THEN
DO;
OUTPUT;
CALL MISSING(%List_Vars(Comma=YES));
END;
RUN;
**------------------------------------------------------------------------------**;
&Cmnt.PROC PRINT DATA=WORK.FINAL_DATA;
&Cmnt.RUN;
**------------------------------------------------------------------------------**;
%Time_Stamp(STOP);
Jim
SAS INNOVATE 2024
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Back in the Classroom!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
7466 views
-
8 likes
-
-
